How to restore a Custom menu if a user presses Cancel after selcting exit

[Alseikhan]

A workbook has the subroutine attached to workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

to delete custom menu before closing the workbook.
However, if a user changes mind and presses Cancel to keep workbook
open, the custom menu is deleted anyway!

How do you solver this problem?

Thank you.
Alseikhan

 

[Guest]

Typically I add controls to the Worksheet Menu Bar instead of making custom
toolbars but the same holds for toolbars. I create them programmatically on
wb_open and make them Temporary. I make them invisible on wb_deactivate and
visible again on wb_activate. So when the application is closed, they are
deleted since they are Temporary. They are, of course, recreated when the
particular wb is opened.

If the user has more than one wb open then, if they activate a different wb,
the commandbar is made invisible (wb_deactivate event) so that they cannot
run a macro that's inappropriate for the other wb. They are made visible
again when the user returns to the wb (wb_activate event).

Works fine so far.

Regards,
Greg

 

[Doug Glancy]

Alseikhan,

Try something like this. It replaces Excel's prompt with a custom one, and
uses a global variable that is passed to the Deactivate event, which happens
when the workbook is really closing:

Option Explicit
Dim wb_closing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wb_saved As Boolean
Dim save_wb_or_cancel As VbMsgBoxResult

wb_saved = ThisWorkbook.Saved
If Not wb_saved Then
ThisWorkbook.Saved = True
save_wb_or_cancel = MsgBox(prompt:="Do you want to save changes to " &
"""" & ThisWorkbook.Name & "?""", _
Buttons:=vbExclamation + vbYesNoCancel + vbDefaultButton1,
Title:="LOOKS LIKE EXCEL")
Select Case save_wb_or_cancel
Case vbYes
With ThisWorkbook
.Save
.Close
End With
wb_closing = True
Case vbNo
ThisWorkbook.Close
wb_closing = True
Case vbCancel
Cancel = True
End Select
Else
wb_closing = True
End If

End Sub

Private Sub Workbook_Deactivate()

If wb_closing Then
MsgBox "really closing"
Call DeleteMenu
End If

End Sub

hth,

Doug

 

[Doug Glancy]

A couple of errors in the previous post. Use this instead for the
BeforeClose. Also be sure to test this to make sure I haven't made any
other mistakes, otherwise you could lose data:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wb_saved As Boolean
Dim save_wb_or_cancel As VbMsgBoxResult

wb_saved = ThisWorkbook.Saved
If Not wb_saved Then
ThisWorkbook.Saved = True
save_wb_or_cancel = MsgBox(prompt:="Do you want to save changes to " &
"""" & ThisWorkbook.Name & "?""", _
Buttons:=vbExclamation + vbYesNoCancel + vbDefaultButton1,
Title:="LOOKS LIKE EXCEL")
Select Case save_wb_or_cancel
Case vbYes
ThisWorkbook.Save
wb_closing = True
Case vbNo
wb_closing = True
Case vbCancel
ThisWorkbook.Saved = False
Cancel = True
End Select
Else
wb_closing = True
End If

End Sub

 

[Doug Glancy]

Alseikhan,

I should have said that Greg's method is better than my solution. My more
complicated method can be useful if your menus were built in an addin that's
opened with the workbook, but that doesn't seem to apply to what you are
doing.

Doug