How to make CommandButton private to designated workbook?

[EK1]

Hi,

I am able to program command buttons to appear in the toolbar when a specific workbook is opened and it nicely disappears once the workbook is closed.

My problem is that whilst that button having workbook is open and the user opens another excel workbook, the button appears there too and the code can be run without any problems there also
How do I stop this? The button should ONLY appear on that workbook even if multiple workbooks are open. Is this possible?

I put buttons in using the following code:

Please Login or Register  to view this content.

[shg]

Remove the toolbar in the Workbook_Deactivate event and enable it in the Workbook_Activate event.

[EK1]

I call the above code from the workbook area of code with the following:

Please Login or Register  to view this content.

So I'm guessing instead of workbook_open I should change it to workbook_activate?

[shg]

Not sure if the Workbook_activate event fires when a workbook opens. Try it. If not, put it in both places.

[EK1]

I'm using the following code and it works perfectly. Exactly what I wanted. Thanks shg for the quick help.

Please Login or Register  to view this content.

[shg]

Good job !

[EK1]

Before I mark this solved, I noticed something else with these buttons.

When someone modifies a cell, then clicks X in the corner to close then click CANCEL instead of SAVE or DO NOT SAVE.....the toolbar disappears. The before close method runs and the toolbar is gone. Is there any way to avoid this as it can get annoying.

[shg]

I don't see the Close event triggering if you press the Cancel button. Figure out what's happening.

[romperstomper]

Check the Saved property of the workbook in your BeforeClose event and then do your own prompting as to whether they want to save. That way you will know whether or not to delete the toolbar.

[EK1]

This fixes the problem. Thanks to the interwebz.

Please Login or Register  to view this content.