Workbook BeforeClose Cancel As Boolean

Workbook_BeforeClose occurs when a workbook is closed. Cancel set to True prevents the workbook from closing.

If the Open event is used to create a custom menu, then the BeforeClose event is used to delete it:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbWSMenuBar As CommandBar On Error Resume Next

Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("MrExcel Programs").Delete End Sub

This is a nice little procedure, but there is one problem: If changes are made to the workbook and it isn't saved, Excel pops up the Do You Want to Save? dialog box. This dialog box pops up after the BeforeClose event has run. So, if the user decides to Cancel, the menu is now gone.

The solution is to create your own Save dialog in the event:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String Dim Response

Dim cbWSMenuBar As CommandBar If Not ThisWorkbook.Saved Then

Msg = "Do you want to save the changes you made to " & Me.Name & "?" Response = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Response Case vbYes

ThisWorkbook.Save

Case vbNo

ThisWorkbook.Saved = True Case vbCancel

Cancel = True Exit Sub End Select

End If

On Error Resume Next

Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("MrExcel Programs").Delete End Sub

0 0

Post a comment