Workbook Closing closes other open workbooks

S

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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
 
Upvote 0
Paul Ked said:
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
Click to expand...
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Yes, missed that one. It was a bit muddled up, especially having a label named Else. Are you sorted now?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Automatically Copying Data To A New Workbook When Closing A Workbook
Replies
4
Views
385
Tmini
T
KlausW
  • Solved
copy from a closed Workbook error
Replies
6
Views
437
KlausW
KlausW
I
  • Solved
Two workbooks open & need to close & save one using vba
Replies
1
Views
197
Alex Blakenburg
A
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
240
Hayf13
H
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
279
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top