Workbook Close Error

[sassriverrat]

The last error (that I can find) in my workbook seems to be associated/connected with closing the workbook if a userform is "running".

When the workbook is first opened, it hides the excel sheet (application.visible) and runs a series of userforms, thus appearing as an application instead of an excel sheet. However, if I close one of the userforms (using the "X" in the top, right-hand corner), then excel has an error while closing. If I hit "debug", it momentarily opens VBA editor and then everything closes, so I never actually get to see what bugged out.

I have attached the closing code as well as the code from one of the userforms. This workbook is called "Master Voyage Report" and when named as such, only has three sheets, each named "Developer", "Notes", and "Ports". The other sheets, "Noon", "Noon#", and "Arrival" do not yet exist. When they are created, the workbook is renamed.

Error Code 424 "Object Required" and it happens on any of the userforms and the workbook can be renamed in it's other names and it still happens.

rivate Sub Workbook_BeforeClose(cancel As Boolean)
    'On Error GoTo Helper
    Dim sh As Worksheet
'This is the last event to run as Excel is closing
    Sheets("Notes").Visible = xlVeryHidden
    Sheets("Developer").Visible = xlVeryHidden
'Kills Noon/Arrival Sheets to Preserve Template
    If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
        Application.DisplayAlerts = False
        Sheets("Developer").Range("A2:D3,A5:D5,A7:D7").ClearContents
        For Each sh In ThisWorkbook.Worksheets
            If LCase(sh.name) Like "noon*" Then
                If Len(sh.name) > 4 Then
                    If IsNumeric(Mid(sh.name, 5, Len(sh.name))) Then sh.Delete
                Else
                    sh.Delete
                End If
            End If
        Next
        If SheetExists("Arrival") Then Sheets("Arrival").Delete
        If SheetExists("Voyage Specifics") Then Sheets("Voyage Specifics").Delete
    Else
        Sheets("Ports").Visible = xlVeryHidden
    End If
    Sheets("Developer").Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
    Application.DisplayAlerts = True
    ActiveWorkbook.Save
'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 [1014] 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 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 [1060] 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

 

[Yongle]

Use this to prevent the user closing userform with top-right X ....
- goes in userform module

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
End Sub

An alternative way to close the userform ...
- goes in userform module and is activated when CommandButton1 is clicked

Private Sub CommandButton1_Click()
    Unload Me
End Sub

 

[sassriverrat]

Well I actually have a command button that does just that.
Ideally, I’d like the “x” of the userform to close the workbook is selected but I don’t know what’s kicking an error.....

 

[Yongle]

Determine which line is causing the error
One way to narrow it down is to use a message box - keep moving it down the code until it no longer appears when you run the code. Some trial & error should not take too long

 

[sassriverrat]

Excellent idea! Easy way to troubleshoot.

So I came up with this. Below is my line of coding. This module is called during the workbook_open event. It's redundant but just a carry over from my coding on other stuff.

It is error'ing when trying to delete buttons. However, my thought here is that the buttons may not exist unless the program has fully run. Maybe I'm totally off on this idea.

Also, my "Helper" coding reports an error of 1010 which isolates this module as the problem child. I can see the first msgbox but not the second.


see below:

Sub SetButtons()

On Error GoTo Helper
        Dim a As Button
        Dim b As Button
        Dim c As Button
        Dim d As Button
        Dim e As Button
        Dim f As Button
        Dim j As Button
        Dim k As Button
        Dim l As Button
        Dim m As Button
        Dim n As Button
        Dim o As Button
        Dim p As Button
        Dim q As Button
        Dim r As Button
        Dim s As Button
        Dim t As Button
        Dim u As Button
        Dim v As Button
        Dim x As Button
        Dim y As Button
        Dim z As Button
        Dim aa As Button
        Dim ab As Button
        Dim ac As Button
        Dim ad As Button
        Dim ae As Button
        Dim af As Button
        Dim ag As Button
        Dim w As Worksheet
        Dim w1 As Worksheet
        Set w = Sheets("Developer")
        Set w1 = Sheets("Notes")
        
Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
Sheets("Notes").Unprotect Password:=Worksheets("Developer").Range("B17:E17").Value
MsgBox ("Hi1") 'this line is observed, so error is after.
        w.Buttons.Delete
        w1.Buttons.Delete
MsgBox ("Hi2") 'This line is not observed, so error is before
        Set a = w.Buttons.Add(30, 345, 150, 25)
        a.OnAction = "DemoTest"
        a.Characters.Text = "Registration"

        Set b = w.Buttons.Add(190, 345, 150, 25)
        b.OnAction = "ClearRegistry"
        b.Characters.Text = "Clear Registration"

        Set c = w.Buttons.Add(30, 385, 150, 25)
        c.OnAction = "Transfer1"
        c.Characters.Text = "Make a Program Copy"
        
        Set d = w.Buttons.Add(190, 385, 150, 25)
        d.OnAction = "Terminate"
        d.Characters.Text = "Delete All Active Directories"

        Set e = w.Buttons.Add(30, 425, 310, 25)
        e.OnAction = "Terminate_Program"
        e.Characters.Text = "Terminate Program"
                        
        Set f = w.Buttons.Add(375, 25, 150, 25)
        f.OnAction = "VoyageSpecifics"
        f.Characters.Text = "New Voyage Specifics Sheet"
        
        Set j = w.Buttons.Add(375, 65, 150, 25)
        j.OnAction = "Addnoonsheet"
        j.Characters.Text = "New Noon Sheet"
        
        Set k = w.Buttons.Add(375, 105, 150, 25)
        k.OnAction = "addnoonssheet"
        k.Characters.Text = "New Noon# Sheet"
        
        Set l = w.Buttons.Add(375, 145, 150, 25)
        l.OnAction = "arrivalsheetmaker"
        l.Characters.Text = "New Arrival Sheet"
        
        Set m = w.Buttons.Add(375, 225, 150, 25)
        m.OnAction = "Removeprotection"
        m.Characters.Text = "Unprotect All Sheets"
        
        Set n = w.Buttons.Add(375, 265, 150, 25)
        n.OnAction = "master_reset"
        n.Characters.Text = "Reset Master Template"
        
        Set o = w.Buttons.Add(375, 185, 150, 25)
        o.OnAction = "testcreator"
        o.Characters.Text = "Test Voyage"
        
        Set p = w.Buttons.Add(375, 305, 150, 25)
        p.OnAction = "Kill_error_log"
        p.Characters.Text = "Delete Error Log"
        
        Set q = w.Buttons.Add(375, 345, 150, 25)
        q.OnAction = "Email_Developer"
        q.Characters.Text = "Email Error Log"
        
        Set r = w.Buttons.Add(375, 385, 150, 25)
        r.OnAction = "Open_error_log"
        r.Characters.Text = "View Error Log"
        
        Set s = w.Buttons.Add(375, 425, 150, 25)
        s.OnAction = "DevSave"
        s.Characters.Text = "Save"
        
        Set t = w.Buttons.Add(375, 465, 150, 25)
        t.OnAction = "Quitter"
        t.Characters.Text = "Quit"
        
        Set u = w1.Buttons.Add(1095, 388, 143, 16)
        u.OnAction = "LocalFolderGen"
        u.Characters.Text = "Generate Local Folder"

        Set v = w1.Buttons.Add(1095, 405, 143, 16)
        v.OnAction = "NetworkFolderGen"
        v.Characters.Text = "Generate Network Folder"
        
        Set x = w1.Buttons.Add(1095, 422, 143, 16)
        x.OnAction = "ArchiveFolderGen"
        x.Characters.Text = "Generate Archive Folder"
        
        Set y = w1.Buttons.Add(1095, 439, 143, 16)
        y.OnAction = "DeleteLocalFolderGen"
        y.Characters.Text = "Delete Local Folder"
        
        Set z = w1.Buttons.Add(1095, 456, 143, 16)
        z.OnAction = "DeleteNetworkFolderGen"
        z.Characters.Text = "Delete Network Folder"
        
        Set aa = w1.Buttons.Add(1095, 473, 143, 16)
        aa.OnAction = "DeleteArchiveFolderGen"
        aa.Characters.Text = "Delete Archive Folder"
        
        Set ab = w1.Buttons.Add(875, 405, 193, 41)
        ab.OnAction = "DefaultSystems"
        ab.Characters.Text = "Generate Default Directory"
        ab.Font.FontStyle = "Arial"
        ab.Font.FontStyle = "Bold Italic"
        ab.Font.Size = 16
        ab.Font.ColorIndex = 1
        
        Set ac = w1.Buttons.Add(875, 448, 193, 41)
        ac.OnAction = "DailySaver"
        ac.Characters.Text = "Save + Quit"
        ac.Font.FontStyle = "Arial"
        ac.Font.FontStyle = "Bold Italic"
        ac.Font.Size = 36
        ac.Font.ColorIndex = 3
        
        Set ad = w.Buttons.Add(375, 505, 150, 50)
        ad.OnAction = "PrimarySaver1"
        ad.Characters.Text = "Primary Save Point on Network Drive"
        
        Set ae = w.Buttons.Add(375, 570, 150, 50)
        ae.OnAction = "PrimarySaver2"
        ae.Characters.Text = "Primary Save Point on Local Drive"

    
'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 [1010] 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

 

[Yongle]

I suspect that line below is where the code is failing because worksheet w is still protected

Set a = w.Buttons.Add(30, 345, 150, 25)

Are these lines unprotecting your sheets?

Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
Sheets("Notes").Unprotect Password:=Worksheets("Developer").Range("B17:E17").Value

To test ...
- add these 2 lines immediately below those lines and run the code

MsgBox w.Name & " is protected = " & w.ProtectContents
MsgBox ws.Name & " is protected = " & ws.ProtectContents

I expect both message boxes to return TRUE but the code requires them to return FALSE - buttons cannot be added to a protected sheet

Observations ...
1. The password in each case should be the value in a single cell not a range (or it should be hard-wired into the code)
2. Make use of declared variables i.e w.Unprotect... and ws.Unprotect ... (this is not the reason for the code failing)

 

[sassriverrat]

Shoot. I forgot to highlight the lines in my previous post where the error occurs.
So to answer your question- the msgbox do come back false- both of them.
Further, the w.buttons.delete is where the code is failing. If the workbook is allowed to fully start up- then there is no error. But the error only occurs when closing a userform during the startup process.

 

[Yongle]

1. Which error do you get if you disable this

On Error GoTo Helper

in BOTH Workbook_BeforeClose AND Userform_QueryClose

Disable with apostrophe like this

'On Error GoTo Helper

2. Which line is highlighted when code stops ?

 

[sassriverrat]

well see that's just the thing.... I tested already with both iterations of the "On Error" disabled and still didn't get the error. I can kick up the debug window but hitting "debug" still ends up closing excel out (as if the problem was solved. I have taken a partial copy from the "Set Buttons" and highlighted again the lines error'ing. This is found only because of the msgbox.

I guess you can't color within code anymore....anyway

w.Buttons.Delete (ERROR CODE)

        Dim w As Worksheet
        Dim w1 As Worksheet
        Set w = Sheets("Developer")
        Set w1 = Sheets("Notes")
w.Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
w1.Unprotect Password:=Worksheets("Developer").Range("B17:E17").Value
MsgBox w.name & " is protected = " & w.ProtectContents
MsgBox w1.name & " is protected = " & w1.ProtectContents
       w.Buttons.Delete
MsgBox "Hey"
        w1.Buttons.Delete
MsgBox "hi"
        Set a = w.Buttons.Add(30, 345, 150, 25)
        a.OnAction = "DemoTest"
        a.Characters.Text = "Registration"

        Set b = w.Buttons.Add(190, 345, 150, 25)
        b.OnAction = "ClearRegistry"
        b.Characters.Text = "Clear Registration"

 

[Yongle]

In post#5 you said
This module is called during the workbook_open event. It's redundant but just a carry over from my coding on other stuff

So why are you calling it in Workbook_Open ?