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











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks