Userform close throwing error 424

sassriverrat

Well-known Member
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!)

Code:
 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
Code:
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
 

sassriverrat

Well-known Member
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
Code:
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

Well-known Member
solved.

Code was wrong in the userform-

Issue was
Code:
If closemode = 0 Then
"Code that closes workbook"
ENd If
where ist should have been = 1
 

sassriverrat

Well-known Member
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.
 
Last edited:

sassriverrat

Well-known Member
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:

Code:
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top