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

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

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

"Alseikhan" wrote:

>
> 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
>
>
> --
> Alseikhan
> ------------------------------------------------------------------------
> Alseikhan's Profile: http://www.excelforum.com/member.php...o&userid=32364
> View this thread: http://www.excelforum.com/showthread...hreadid=529491
>
>

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


"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Alseikhan" wrote:
>
>>
>> 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
>>
>>
>> --
>> Alseikhan
>> ------------------------------------------------------------------------
>> Alseikhan's Profile:
>> http://www.excelforum.com/member.php...o&userid=32364
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=529491
>>
>>

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


"Alseikhan" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> Alseikhan
> ------------------------------------------------------------------------
> Alseikhan's Profile:
> http://www.excelforum.com/member.php...o&userid=32364
> View this thread: http://www.excelforum.com/showthread...hreadid=529491
>

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


"Doug Glancy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Alseikhan" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> Alseikhan
>> ------------------------------------------------------------------------
>> Alseikhan's Profile:
>> http://www.excelforum.com/member.php...o&userid=32364
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=529491
>>
>
>