Userform close throwing error 424

[sassriverrat]

Good Morning,

My pride and joy massive workbook started kicking an error for one of the employees and I haven't realized why yet.

As some of you know, when opening the workbook, it runs a series of useforms so that the workbook looks and runs like a "real" program, not an excel book. Anyway, if one of the userforms during the startup process is closed (while the application is still set to invisible), it closes the userform, per design. However, in closing, I'm getting an error code- 424 Object required. When I figure out where it's coming from- I realize it's taking me to a page that makes buttons on two different pages. That piece of code is set to run when the workbook starts up. Trying to debug the error has been tough....closing the userform results in the debug message popping up. I hit debug and VBA editor opens for about 5 seconds, then closes itself out (as if fixing itself) and the program closes. Any ideas what might cause this? The button making code is below as well as the startup code. Thanks!

Note: I've take the "on error goto" lines out so I could try to debug....but my "helper" lines of code are the only reason I could find where the eror was to begin with (every piece of code has it's own number...so you can see how much code is in this workbook!)

 Sub Ports_Instructions()

'Begins Error Handling Code
'On Error GoTo Helper


    Dim w As Worksheet
    Set w = Sheets("Ports")
    Dim k As Button
    Dim l As Button
    Dim m As Button
    
    w.Buttons.Delete
     
    Set k = w.Buttons.Add(540, 75, 230, 75)
        k.OnAction = "Instructional"
        k.Characters.Text = "Instructions"
        k.Font.FontStyle = "Bold"
        k.Font.Size = 40
        k.Font.ColorIndex = 3
        
    Set l = w.Buttons.Add(540, 170, 230, 75)
        l.OnAction = "Alphabetize"
        l.Characters.Text = "Alphabetize"
        l.Font.FontStyle = "Bold"
        l.Font.Size = 40
        l.Font.ColorIndex = 50
        
    Set m = w.Buttons.Add(540, 265, 230, 75)
        m.OnAction = "Save_As"
        m.Characters.Text = "Save + Quit"
        m.Font.FontStyle = "Bold"
        m.Font.Size = 40
        m.Font.ColorIndex = Black
    
'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1127] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
        
End Sub

Private Sub Workbook_Open()

'On Error GoTo Helper


'This will show the Userform 1 when starting and hide the excel sheet in the background
'Also checks to see if program has ever been run, and if not, loads program for the first time
Application.Visible = False
Dim s As String
Dim namer As String
Dim d As Variant
Dim ExpirationDate As Date
Dim pass1 As String
Dim pass2 As String




With Sheets("Developer")
    s = .Range("B34")                   'username
    'pass1 = .Range("B15:E15").Value     'sheet unprotect
    'pass2 = .Range("B39").Value         'date password
    ExpirationDate = .Range("E37")      'expiration date
    d = .Range("B39").Value             'registration key
    initialdate = .Range("C36")         'initializing date
End With




'ExpirationDate = (Sheets("Developer").Range("E37"))     'expiration date
'edate = Sheets("Developer").Range("E37")
namer = Sheets("Notes").Range("N4")                     'just a name






s = GetSetting("DemoTest", "Registration", "Username")
'Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
If s = "" Then
        Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
        Sheets("Developer").Range("B34:F34").ClearContents
        's = InputBox("Welcome to the " & namer & " Voyage Reporting System." & vbCrLf & "Please input the appropriate name to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", namer, "Bridge")
        'UserForm17.Show
        s = cInputBox()
        'MsgBox s
        
        If s <> "" Then
            's = cInputBox()
            Sheets("Developer").Range("B34") = s
            SaveSetting "DemoTest", "Registration", "Username", s
            Sheets("Notes").Visible = xlSheetVisible
            Sheets("Notes").Select
            Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            Sheets("Developer").Range("C36") = Date
            'If s <> "" Then MsgBox "Welcome to the " & name & " Voyage Reporting System." & vbCrLf & "Please input the appropriate data to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", vbOKOnly, name
            Application.Visible = True
        End If


Else:
    If ExpirationDate > Date Then
        If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
             UserForm1.Show
        ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
             UserForm2.Show
        Else: UserForm3.Show
        End If
    Else:
TryAgain:
        d = InputBox("Your workbook date has expired. Please enter the registration key to renew your license.", namer)
        If d = vbCancel Then GoTo Closer
        If d = "" Then GoTo Wrong
        If d = CStr(Worksheets("Developer").Range("B39").Value) Then
            Sheets("Developer").Range("C36") = Date
            MsgBox "Welcome Back " & s, vbOKOnly, namer
        Else: GoTo Wrong
        End If
        If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
             UserForm1.Show
        ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
             UserForm2.Show
        Else: UserForm3.Show
        End If
    End If
        
End If




'Application.Visible = True   'inserted just to check workbook
'Protects/Hides sheets on startup
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.name = "Notes" Then
            sh.Protect Password:=Worksheets("Developer").Range("B17:E17").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Ports" Then
            sh.Protect Password:=Worksheets("Developer").Range("B19:E19").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Developer" Then
            sh.Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            End If
    Next sh
    
'Sets buttons on Developer sheet
Call SetButtons
'Sets buttons on Ports Sheets
Call Ports_Instructions
    


Exit Sub
'Closing code
Closer:
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If


Exit Sub
'Error Clearing Code
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1011] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
        
Exit Sub
'Wrong coding
Wrong:
            MsgBox "Password Incorrect, Please try again.", vbCritical, namer
            GoTo TryAgain
    
End Sub

 

[Norie]

Have you tried stepping through the code starting in Workbook_Open?

 

[sassriverrat]

I don't entirely understand the stepping through because I don't see anything off, at least not at first glance...but it still happens.

Another issue that just started....I'm getting errors where stuff like

On Error Goto Helper

code code code

Exit Sub 

Helper:
resp (whatever I want the message to be)

and it says that the "On Error" line isn't defined, the "resp" isn't defined, etc....Again not sure what's starting to cause this.

 

[sassriverrat]

solved.

Code was wrong in the userform-

Issue was

If closemode = 0 Then
"Code that closes workbook"
ENd If

where ist should have been = 1

 

[sassriverrat]

Nope. Nevermind.

Now I'm up a creek....

So by correcting that, when the workbook opened (and went right into the userform), closing the userform closed excel, exactly as intended. However, now, if I do anything, when the userform disappears, excel closes.

The only button that invokes a different response should bring up another userform, however, instead it brings up a VBA runtime erorr -2147418105 (80010007): Automation Error- The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.

Hitting debug brings up the vba editor and then it closes...so effectively locked out of the workbook.

 

[sassriverrat]

I've gotten back into the workbook and gotten all fo the closemode = 1 back to = 0. Now I still get the error 424 when closing a userform (which closes the workbook) but it still closes the workbook.
Here's an example:

Private Sub Userform_QueryClose(cancel As Integer, closemode As Integer)On Error GoTo Helper
If closemode = 0 Then
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
End If
'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1026] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub