BeforeClose and Cancel (Excel 2000, SR1)

[JIMbythebay]

Hi Gang,

I have a template that generates its own toolbar on the fly, and cleans up when the workbook closes. To do so, I have a CommandBars("SuchAndSuch").Delete in the Workbook_BeforeClose event.

However, if the user accidentally closes the workbook and gets prompted about saving before they quit, they can cancel the accidental Close. By then, the toolbar is gone. What event can I monitor to re-generate the toolbar in such a situation?

Thanks!

[MichaelRead]

Hi Jim,

I have a similar instance and I have not gotten around to solving it yet, but I am thinking that if you use
<pre>If Cancel = True then <font color=red>call create toolbar sub<font color=red>
</pre>


It will recreate the toolbar if the user decides not to close the book. There may be some other way out there, but off the cuff, this is all I can think of.

[pieterse]

Here is one way to solve that.

In your before close event, enter this line:

Application.Ontime Now, "ReInstate"

ReInstate is a Sub in a normal module that re-initializes your application completely.

What happens is, your workbook closes normally, but an event is set to run a macro from your workbook, which forces it to reload (unfortunately also triggering the macro warning again). If however the closure is caused by a termination of Excel, nothing happens after the closure of your workbook, XL terminates normally.
It is a tricky routine however, because after firing your ReInstate routine, the workbook_open event will also fire. You'll have to set a global boolean variable in ReInstate so the workbook Open event knows something already has been done.

To avoid the macro warning message, save your workbook as an add-in and treat it as such by installing it from Tools, Add-ins..

[klyjen]

Hi JIM--I am trying to do exactly what you say you've done, and I can't do it (see the thread I started today)...would you mind posting your code or attaching your workbook? I am going nuts....

Thanks.

klyjen

[JIMbythebay]

Here is the workbook. I don't think I have implemented either idea presented in this thread, and I don't have time now to look. But here it is, for your reading pleasure.

[LegareColeman]

I solved that problem by using the workbook Activate and Deactivate events to do my menu customizations.

[Hans Pottel]

As Legare said, the way to do it is using the deactivate event:

Declare the variable CloseWB as boolean in a general module.

Dim CloseWB as Boolean

<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = False
CloseWB = Not Cancel
End Sub
</pre>


<pre>Private Sub Workbook_Deactivate()
On Error Resume Next
If CloseWB = True Then
Application.CommandBars("MyToolbar").Delete
Else
Application.CommandBars("MyToolbar").Visible = False
End If
On Error GoTo 0
End Sub
</pre>


When one presses Cancel when he/she is prompted with the Excel message about saving, then the toolbar is not deleted. When choosing for closing, the workbook will be deactivated, which calls the deactivate event, where the toolbar is deleted. If you are simply leaving your workbook by activating another one, the toolbar will only be hidden. Of course, you must unhide it when the workbook is activated again, using the code below:

<pre>Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("MyToolbar").Visible = True
On Error GoTo 0
End Sub
</pre>

[JIMbythebay]

Thanks Legare and Hans,

I fixed my sample by simply eliminating the code for Workbook_BeforeClose event. If the user closes, then presses cancel when prompted for saving changes, the Workbook_Deactivate is not executed and the toolbar remains intact. If they choose Yes or No, the Workbook_Deactivate is executed and the toolbar is removed.

[LegareColeman]

If you didn't, you should probably also move your code to display your toolbar/menu to the Activate event. If you didn't do that, then if two workbooks are open, you will lose your toolbar/menu if you switch to it and won't get it back when you switch back.

[JIMbythebay]

My workbook already has the necessary code in the Workbook_Activate and Workbook_Deactivate to cover these situations. The problem was, the code in BeforeClose was not necessary.

Thanks!

[klyjen]

Thanks Jim! As it turns out, the problem I was having was with our DMS. Grr. But your code will be a good reference, so I appreciate it...

~jen