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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
58
HeinrichPaul
H
I
  • Solved
Copy and Paste from One workbook to another
Replies
3
Views
428
ipon70
I
S
  • Solved
Need macro to only close workbook at the end if it wasn't already open when Workbook Open Event runs
Replies
5
Views
178
sspatriots
S
J
  • Question
Run time error with VBA to copy Chart objects from closed workbook
Replies
4
Views
106
Domenic
D
V
  • Solved
Workbook with Timer Macro Saving After Closing
Replies
1
Views
415
John_w
J

Forum statistics

Threads
1,214,611
Messages
6,120,509
Members
448,967
Latest member
screechyboy79

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