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

 

[sassriverrat]

Good question. So for some reason, when I closed my workbook, it usually would open exactly as it was supposed to. However, at other times, or if transferring between PC’s (particularly with different copies of excel), the buttons would get rearranged, resized, repositioned, etc and would drive me bananas. So this ensured, despite having tried “locking” the buttons to no avail, that they would always be correct.

 

[Yongle]

Size and positioning can be flaky with different versions of Excel - I have experienced that in the past

Step1
Delete the line in Workbook_Open which is calling SetButtons
Does that make the original problem go away ?

Step2
Test SetButtons standalone (ie not in workbook_open)
I expect it to fail when you run it

 

[sassriverrat]

So if I remove it from Workbook_open then the problem goes away.

However, if I don't close one of the userforms and instead let the workbook fully open (run through the userforms), then it doesn't have any problems either (no problems running in Workbook_open) and runs fine as a standalone..... it's only if I close it while a userform is running.

Note: When the userforms are running, the application is hidden. Not sure if that would do anything. But basically not sure how to get this to run except that I might incorporate it into my "registration" code that runs automatically first time the workbook runs on a machine (a crude form of a registry file).

 

[Yongle]

As mentioned earlier in the thread ... I do not understand how this code could work

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

Try replacing the range with the actual password for each sheet

Sheets("Developer").Unprotect Password:="put correct password here"
Sheets("Notes").Unprotect Password:="put correct password here"

 

[sassriverrat]

So the code change reports the same result: sheet unlocks as it should, no problems there. It seems my error comes in this section and I'm not sure how to get around it. Is there a way to tell excel that if a button is not in a specified location or a specified size, then to run my code? Workbook_open seemed to be the easy way of doing it....but it doesn't get along with userforms that are being closed. Also, if I open and then close one of the userforms after the workbook has had time to fully open, no problem.

ws.buttons.delete

 

[sassriverrat]

So the code change reports the same result: sheet unlocks as it should, no problems there. It seems my error comes in this section and I'm not sure how to get around it. Is there a way to tell excel that if a button is not in a specified location or a specified size, then to run my code? Workbook_open seemed to be the easy way of doing it....but it doesn't get along with userforms that are being closed. Also, if I open and then close one of the userforms after the workbook has had time to fully open, no problem.

ws.buttons.delete

This really seems like an error between the userforms and the workbook_open anyway....Here's how the workbook is designed to work:
1. Upon "opening" the workbook, workbook is immediately hidden and userform1, userform2, OR userform3 will show (depending on the name of the workbook)
1a. Closing any of these three userforms will result in this error
2. If userform1 will show if the workbook is named "Master Voyage Report"
2a. Clicking the "Next" button in userform1 (simple "unload me" and "userform4.show") will bring up userform4. Workbook itself is still hidden with application.visible = false.
2b. Closing userform1 via "x" will result in error as stated above in 1a.
3. Userform4, now visible, allows user to input a series of values into a sheet (that's still not visible).
3a. Closing this userform via the "x" will result in error as stated above in 1a. and 2b.
3b. Hitting "Next" in userform4 closes userform (unload me) and makes excel visible again (application.visible = true)

All of these userforms use the same code for the "x" to close the workbook so I don't know why it's an issue. That code, as previously stated, is

If closemode = 0 Then
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
End If

 

[Yongle]

Try inserting this as first line in Workbook_BeforeClose

    Application.Visible = True

 

[sassriverrat]

Try inserting this as first line in Workbook_BeforeClose

    Application.Visible = True

So I tried that but to no avail.

I moved my "Call Buttons" line within the Workbook_Open up to be the first thing that happens when the workbook opens and it seems to have fixed the problem....for now. We shall see. Certainly an interesting error though!

 

[Yongle]

I moved my "Call Buttons" line within the Workbook_Open up to be the first thing that happens when the workbook opens and it seems to have fixed the problem....for now. We shall see. Certainly an interesting error though!

Easy fix - if that is all it takes!

 

[sassriverrat]

Easy fix - if that is all it takes!

Seems to be....just wish I knew why it was bugging before.