Hi folks, hoping someone can help me with this.
I have a workbook with a _BeforeClose event which fires twice, but only when the Excel application itself is closing. When the event fires the second time, the workbook is already "gone" from Excel and the objects it is referencing do not exist, leading to a runtime error.
When I close the workbook WITHOUT closing Excel, everything functions as expected.
As you can see, the Workbook_Close event and the doShutDown() function below both make calls to routines in other code modules. I have not supplied these here as there are a few. I have however tried removing these calls, but doing so does not fix the 'double-firing' behaviour so I think these can be safely eliminated from suspicion.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Now < ncb + TimeSerial(0, 0, 3) Then Cancel = True: Exit Sub
Init.setGlobals False, False
If [s_forceclose] = True Then
If userID = Developer Then
MsgBox "Would now close", vbInformation, dlt
[s_forceclose] = False
Cancel = True
Else
Cancel = False
Me.Saved = True
End If
Else
Cancel = Not doShutDown()
If Not Cancel And Not Me.Saved Then
If Application.userName = Developer Then
Select Case MsgBox("Save?", vbExclamation + vbYesNoCancel, dlt)
Case Is = vbYes
Me.Save
Case Is = vbNo
Me.Saved = True
Case Else
Cancel = True
End Select
Else
Me.Saved = True
End If
End If
End If
End Sub
Private Function doShutDown() As Boolean
Dim sht As Worksheet
If shtA.btnAdminCommit.Enabled Then
MsgBox "You must either save or undo current user changes prior to closing the Roster Management System", _
vbExclamation, dlt & " - Unsaved User Changes Detected"
doShutDown = False
Else
Activator.lockApp
With wbkRA
.Unprotect sysPass
shtNM.Visible = xlSheetVisible
For Each sht In .Sheets
If Not sht Is shtNM Then sht.Visible = xlSheetHidden
sht.protect Password:=sysPass
Next sht
.protect sysPass, True, False
With .Windows(1)
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End With
doShutDown = True
Activator.unlockApp
End If
End Function
Bookmarks