Workbook Closing closes other open workbooks

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
609
I have the code below in a workbook. When I close the workbook, it typically kills all other open workbooks of excel. However, excel has started opening a blank window (no loaded workbook) in a second window whenever opening any excel file. Anybody have ideas on both of these. Further, sometimes when closing the workbook, it'll kick an error that I can't trace- the error message pops up and then when I hit debug, it's gone. I've tried removing the On Error section of code but to no avail.



Code:
Private Sub Workbook_BeforeClose(cancel As Boolean)On Error GoTo Helper
'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
    End If
    If SheetExists("Voyage Specifics") Then
        Sheets("Voyage Specifics").Delete
    End If
Else: Sheets("Ports").Visible = xlVeryHidden
End If


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
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
Untested, but try:

Code:
Private Sub Workbook_BeforeClose(cancel As Boolean)
    On Error GoTo Helper
'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
                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
    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
 

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
609
Untested, but try:

Code:
Private Sub Workbook_BeforeClose(cancel As Boolean)
    On Error GoTo Helper
'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
                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
    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
So this looks like a slightly cleaned up version of my code. I'll give it a run, but that's what I see. Am I seeing correctly?
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
There were errors in your code which could have been causing Excel to crash and hence closing all other workbooks, I've removed those obvious errors. As I said, I haven't tested it. I have also assumed that you have a UDF called SheetExists.
 

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
609
I do have a function called sheetexists see below

I added back in the "Else Sh.Delete" (see below) as that functionality for delete the appropriate sheets wasn't working.

Rich (BB code):
 Private Sub Workbook_BeforeClose(cancel As Boolean)    On Error GoTo Helper
'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
    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 

'Part of the Error Checking System
Function SheetExists(sheetname As String, Optional Wb As Workbook) As Boolean
    If Wb Is Nothing Then Set Wb = ThisWorkbook
    
    On Error Resume Next
    SheetExists = (LCase(Wb.Sheets(sheetname).name) = LCase(sheetname))
    On Error GoTo 0
End Function
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
Yes, missed that one. It was a bit muddled up, especially having a label named Else. Are you sorted now?
 

Forum statistics

Threads
1,078,150
Messages
5,338,528
Members
399,241
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top