Using BeforeClose for deleting a custom toolbar

  • Thread starter Thread starter daithimcc
  • Start date Start date
D

daithimcc

(Excel X for Mac OSX)

Can anyone tell me what I should be doing to delete a custom toolbar
when a particular workbook closes?

In the workbook module I have:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = False
itsclsing = Not Cancel
End Sub

Private Sub Workbook_Deactivate()
If itsclsing Then
ThisWorkbook.Application.CommandBars("ctoolbar").Delete
End Sub

(I'm not clear exactly how Cancel is working above-perhaps this would
make a solution more obvious. I tried to use itsclsing to record
whether cancel had been pressed).

The above works fine when this is the only workbook open. (If Cancel is
pressed the toolbar remains, it disappears otherwise).
However if I have another workbook open and activated and I quit the
application, I get the usual prompts and e.g. if I choose not to save,
the toolbar is not deleted (it appears the next time I open Excel,
without opening the workbook it's attached to).

I would be grateful for any advice.
 
All you need is

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("ctoolbar").Delete
End Sub
 
Since the user is capable of deleting the toolbar before the workbook is
closed, it's safer to modify this slightly to

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("ctoolbar").Delete
On Error GoTo 0
End Sub
 
Thanks for the posts. Using the method you suggest, the toolbar is
deleted even if the user chooses Cancel when prompted to Save. The
workbook must only delete the toolbar if Cancel has not been pressed in
the Save dialog.
(This is why I was trying to use a variable itsclsing).
Any further suggestions would be appreciated.
Thanks, David
 
But your code was in the BeforeClose event, not BeforeSave.Why would you not
want the toolbar deleted when closing the workbook just because the user
chose not to save it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If the user attempts to close the workbook without having saved changes,
the Before_Close code executes, then s/he is prompted to save. If the
user chooses cancel, then the workbook is left open, but the toolbar is
already deleted.

I've seen a number of workarounds, but AFAIK, there's no bulletproof way
to deal with this on a Workbook level. The Before_Close event is the
first event fired. The Save dialog fires after the Before_Close macro
has executed.
 
When you choose close (beforeclose fires+you delete your toolbar), you
are asked if you want to save. When you choose Cancel, not only does it
not save but it also cancels the close. So, the workbook stays open,
but because beforeclose has already fired, you have already deleted the
toolbar (not the desired behaviour).
 
Back
Top