Solved: Clicking CANCEL After Executing a Command

[Opv]

The following script works fine whenever I close my workbook. However, I have a button on my command bar to "Exit Excel" and if I click that button and then click cancel, part of the code, specifically, the line to delete myToolBar is already executed. Consequently, if I click cancel after clicking on the "Exit Excel" button, my toolbar is gone. Is there a way to detect if "Cancel" is clicked so that either none of the code in this script is run OR myToolBar is automatically recreated?

[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(myToolBar).Delete
Call RestoreToolbars
End Sub
[/VBA]

[Opv]

Well, I've done some further testing and it appears the previously posted code has nothing to do with myTooilBar getting deleted.

I commented out all of the code in both the Workbook_Deactivate and the Workbook_BeforeClose subs to see what would happen by just calling the following code to exit Excel.

[VBA]
Sub closeExcel()
'Close EXCEL without saving the active workbooks!

Application.Quit

End Sub
[/VBA]

Now, after clicking the command button to exit Excel, it calls the above script and when I click "Cancel," myToolBar is still being deleted.

Any idea what might be causing this?

[Opv]

I came up with a work-around by avoiding the Application.Quit command until the choice was already made as to whether to exit or cancel. I created a user form with three buttons, one with the option to SAVE ALL AND EXIT (saves all open workbooks and exits Excel with alerts disabled), one to EXIT WITHOUT SAVING (with alerts disabled) and one to CANCEL, which simply closes the user form.

I'm still curious as to whether there is a way to avoid the original problem.

Opv

[Bob Phillips]

Post the workbook, I am struggling to get it.

[Opv]

I've created a blank workbook to demonstrate the problem rather than sending the large workbook in question.

Basically, you will need to change a cell in Sheets(1) and then click the button on the right to close Excel. You will notice that the toolbar goes away when the dialog box pops up. (If the sheet is not changed, the button closes Excel without a dialog so there is no issue.)

Thanks

Opv

[GTO]

Hi Opv,

Workbook_BeforeClose occurs before Workbook_Deactivate, and Excel's "you made changes...save?" msgbox seems to popup as BeforeClose is exited and before the Deactivate event is entered.

In short, as Workbook_Activate and Workbook_Deactivate happen after the Open and BeforeClose events anyway, I just use these to create/kill the toolbar. Anyways, not well tested, but changing where we choose to kill the commandbar seems to work for me.

Option Explicit
    
Private Sub Workbook_Open()
    
    Sheets(1).Activate
End Sub
    
Private Sub Workbook_Activate()
    
    On Error Resume Next
    Application.CommandBars(myToolBar).Delete
    On Error GoTo 0
    
    Call CreateToolbar
End Sub
        
Private Sub Workbook_Deactivate()
    
    On Error Resume Next
    Application.CommandBars(myToolBar).Delete
    On Error GoTo 0
End Sub
    
Private Sub CreateToolbar()
    
    With Application.CommandBars.Add(Name:=myToolBar, temporary:=True)
    
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Close Excel without saving!"
            .FaceId = 536
            .OnAction = "closeExcel"
            .Width = 70
        End With
        
        .Visible = True
        .Position = msoBarFloating
        .Top = 250
        .Left = 900
    End With
End Sub

Hope that helps,

Mark

[Opv]

Hi Opv,

Workbook_BeforeClose occurs before Workbook_Deactivate, and Excel's "you made changes...save?" msgbox seems to popup as BeforeClose is exited and before the Deactivate event is entered.

In short, as Workbook_Activate and Workbook_Deactivate happen after the Open and BeforeClose events anyway, I just use these to create/kill the toolbar. Anyways, not well tested, but changing where we choose to kill the commandbar seems to work for me.

Hope that helps,

Mark

Indeed, it does. That seems to work fine.

Thanks,

Opv