Workbook_BeforeClose problem

[Amit Shanker]

Running Office XP on Windows XP Home Edition

I am successfully creating a custom toolbar 'on the fly' via an Excel
add-in. However, when I click the close button on the active workbook Excel
prompts me to save it, and at this moment my custom toolbar disappears as it
gets deleted by a Workbook_BeforeClose event.

My question is, if a user clicks 'Cancel' when the workbook save prompt
comes up, how can I still retain my custom toolbar ? In other words, my
toolbar should disappear only when Excel application gets closed.

Thanks,
Amit

 

[Juan Pablo González]

The users closes the addin "manually" using the X button ? if so, then, why
do you have the Addin visible ? the idea is that it should be "invisible" to
the user, and that they could only "close" it using Tools | Addins OR
closing the application.

 

[Amit Shanker]

The add-in is always invisible - it is just creating my toolbar. My question
refers to when the user activates the 'X' button on the workbook he is
working on. My toolbar should not disappear if the user hits 'Cancel' at the
prompt for saving his work.

 

[Juan Pablo González]

It shouldn't. Where do you have your code to delete the toolbar ?

 

[Guest]

One way is to create a prompt in the Workbook_BeforeClose event that asks the user if they want to close the work book
Then you can set Cancel to true before it has a chance to remove your toolbar. Therefore pre-empting excels Save dialog
You can even take care of the save yourself by using Save and setting displayalerts to false all within the Workbook_BeforeClose event.

 

[Amit Shanker]

It's in my add-in's workbook event handler module, as follows :

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandBar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

 

[Rob van Gelder]

Check out the example on my website: Save Changes?

It gets used in the BeforeClose event and handles the "sure? you haven't
saved!" stuff.