Excel 2010 Run-Time error 5

Question

Hello!

Created Excel Auto_Open macro (attached to a macro-enabled template; the Auto_Open displays a form and the form fills in a spreadsheet) and had it running just fine. All of a sudden, when I open the template (and therefore the Auto_Open runs), I get a Run-Time error '5": Invalid procedure call or argument. Clicking Debug brings up the Auto_Open - the first line (Load frmDevScreenStatus is highlighted and mousing over it shows <Object variable or With block variable not set>. Of course, I can see - and display - the form in the VBA editor.

Will I simply have to delete the entire template and its associated macro and start all over, or is there some way to avoid that agony? (I feel like I'm trapped in one of the lower circles of Dante's inferno.)

Thank you very much!

Answer 1

Hmmm, I can't see anything in the code that could cause that error. Is the form name spelled correctly in the calling line?

Answer 2

Interestingly enough, I went back and looked at the last changes I'd made - in the Initialization code - and commented them out. Voila - it works fine! But as I couldn't see any problems, I uncommented them, saved again, and...now it works fine every time. Go figure! One of life's little mysteries, I guess.

Anyway, thanks so much for your help - it got me looking at the code itself - all I can think is that something got corrupted and then somehow fixed.

Answer 3

Thank you very much! Please let me know if you need anything else (the actual file, etc.).

The Auto_Open macro is:

Sub Auto_Open()

Load frmDevScreenStatus <-----this the line it chokes on and results in the error

frmDevScreenStatus.Show

End Sub

Here's the code and a screen shot of the form:

'(General Declarations follow)

Dim intNumberOfRows As Integer

Dim intNewRow As Integer

Dim strScreenID As String

Dim strScreenTitle As String

Dim strSiteName As String

Dim strFindThis As String

Dim intFoundRow As Integer

Private Sub cmdAdd_Click()

'Get current total number of rows

With ActiveWorkbook.Sheets("Screens")

    intNumberOfRows = .Cells(.Rows.Count, "B").End(xlUp).Row

End With

intNewRow = intNumberOfRows + 1

ActiveSheet.Range("B" & intNewRow).Value = txtScreenID.Value & " " & txtScreenTitle.Value

txtScreenID.Value = ""

txtScreenTitle.Value = ""

txtScreenID.SetFocus

End Sub

Private Sub cmdDone_Click()

ActiveWorkbook.SaveAs _

    Filename:="J:\SPAWAR 4825\DO 0005 - AFHE 4.0 Manchester and Rota, Spain\SCADA" & strSiteName & ".xlsm", _

    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True

ActiveWorkbook.Close SaveChanges:=False

Unload Me

End Sub

Private Sub cmdFind_Click()

If txtScreenID.Value <> "" Then

    strFindThis = txtScreenID.Value

ElseIf txtScreenTitle.Value <> "" Then

    strFindThis = txtScreenTitle.Value

Else

    x = MsgBox("Please enter an ID or Title.", vbOKOnly, "Missing Choice")

    txtScreenID.SetFocus

End If

Cells.Find(What:=strFindThis, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

intFoundRow = ActiveCell.Row

End Sub

Private Sub cmdInsert_Click()

If txtAssignee.Value <> "" Then

    ActiveSheet.Range("A" & intFoundRow).Value = txtAssignee.Value

End If

If chkCompleted.Value = "True" Then

    ActiveSheet.Range("C" & intFoundRow).Value = "Completed"

    ActiveSheet.Range("C" & intFoundRow).Style = "Good"

Else

    ActiveSheet.Rows(intFoundRow).Style = "40% - Accent1"

    ActiveSheet.Range("H" & intFoundRow).Style = "Normal"

End If

If chkDebugged.Value = "True" Then

    ActiveSheet.Range("D" & intFoundRow).Value = "Debugged"

    ActiveSheet.Range("D" & intFoundRow).Style = "Accent6"

Else

    ActiveSheet.Range("D" & intFoundRow).Style = "40% - Accent1"

End If

If chkLinked.Value = "True" Then

    ActiveSheet.Range("E" & intFoundRow).Value = "Linked"

    ActiveSheet.Range("E" & intFoundRow).Style = "Note"

Else

    ActiveSheet.Range("E" & intFoundRow).Style = "40% - Accent1"

End If

If txtPeerReviewer.Value <> "" Then

    ActiveSheet.Range("F" & intFoundRow).Value = txtPeerReviewer.Value

    ActiveSheet.Range("F" & intFoundRow).Style = "Accent5"

Else

    ActiveSheet.Range("F" & intFoundRow).Style = "40% - Accent1"

End If

If txtFinalReviewer.Value <> "" Then

    ActiveSheet.Range("G" & intFoundRow).Value = txtFinalReviewer.Value

    ActiveSheet.Range("G" & intFoundRow).Style = "40% - Accent4"

Else

    ActiveSheet.Range("G" & intFoundRow).Style = "40% - Accent1"

End If

If txtComments.Value <> "" Then

    ActiveSheet.Range("H" & intFoundRow).Value = txtComments.Value

End If

End Sub

Private Sub cmdPrint_Click()

'Set print area to include all inserted rows

ActiveSheet.PageSetup.PrintArea = "$A$1:$H$" & ActiveSheet.Range("I2").Value

ActiveSheet.PrintOut

End Sub

Private Sub cmdReset_Click()

txtSiteName.Value = ""

txtScreenID.Value = ""

txtScreenTitle.Value = ""

txtAssignee.Value = ""

chkCompleted.Value = False

chkDebugged.Value = False

chkLinked.Value = False

txtPeerReviewer.Value = ""

txtFinalReviewer.Value = ""

txtSiteName.SetFocus

End Sub

Private Sub txtSiteName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ActiveSheet.Range("A1").Value = txtSiteName.Value & " 4.1.0 Screens Status"

strSiteName = ActiveSheet.Range("A1").Value

End Sub

Private Sub UserForm_Initialize()

Dim intLength As Integer

strSiteName = ActiveSheet.Range("A1").Value

If strSiteName <> "" Then 'Will not be blank if previously saved for the site.

    intLength = Len(strSiteName)

    strSiteName = Mid$(strSiteName, 1, intLength - 21) 'Ignore " 4.1.0 Screens Status" part

    txtSiteName.Value = strSiteName 'And now display in SiteName text box.

    txtScreenID.SetFocus

End If

End Sub

Answer 4

Hello,

that error message means that something is not set up right in the code. A closing End IF may be missing or something like that. Can you post the code?