Results 1 to 2 of 2

Workbook_BeforeClose event fires twice when Excel itself is closing

Threaded View

  1. #1
    Registered User
    Join Date
    09-02-2009
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    77

    Workbook_BeforeClose event fires twice when Excel itself is closing

    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
    Last edited by blackworx; 07-19-2012 at 05:18 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1