Custom TBoolbar Deleted BeforeClose, How do I Reinstate on Cancel?

[TemiU]

I created a custom toolbar for my Excel application which gets created (through VBA code) when the workbook opens and deleted in the BeforeClose event of the Workbook. This is working fine.

I ran into the following issue: If a user clicks close and the workbook has not been saved, he/she is then prompted to save, don't save, or cancel. If cancel is chosen, the workbook is not closed. However, the BeforeClose event fires before the prompt to save, so the toolbar gets deleted. Is there any event that I can harness to recreate the toolbar, should the user cancel the save?

 

[Domenic]

Let's assume that the procedure to delete the custom toolbar is called "DeleteMenu", try...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
    [COLOR=darkblue]Dim[/COLOR] Msg [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Ans [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Me.Saved [COLOR=darkblue]Then[/COLOR]
        Msg = "Do you want to save the changes you made to "
        Msg = Msg & Me.Name & "?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel, "Save Changes?")
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Ans
            [COLOR=darkblue]Case[/COLOR] vbYes
                Me.Save
            [COLOR=darkblue]Case[/COLOR] vbNo
                Me.Saved = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]Case[/COLOR] vbCancel
                Cancel = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    DeleteMenu
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[TemiU]

Thanks, Dominic, that's a great work-around!

 

[Domenic]

You're very welcome! Thanks for the feedback!