custom toolbar (excel 97) (97)

Topic

New Reply

I have a big spreadsheet with several userforms. I used to show the userforms with control buttons in the spreadsheet. I thought that it would be better to have, instead, a custom toolbar to do the same thing, as it could be attached anywhere outside the spreadsheet. No problem with that. I built the toolbar and assigned each command a macro that performs userform.show.

the problem is that I generally update the spreadsheet and save it with another name. When I open the file with the new name, the macros in the toolbar are still referenced to the file with the old name.

How can I avoid that?. I want the toolbar to be available only with my custom spreadsheet and not show up when I open other files. And if I share this file in a network, I want the toolbar to show up In any PC when the file is opened.

Thank you

Reply | Quote

Replies

Attach the toolbar to the workbook (Tools/Customize…, Attach button).
Then write code to destroy the tool bar when you close the workbook.

In the VBE editor, double click This Workbook and add code like the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(“myToolbar”).Delete
End Sub

This way, the custom toolbar is only active when the workbook it is stored in, is opened.

A drawback is, that if the user cancels the closing of the workbook (when being asked whether he/she wants to save it), the toolbar has already been removed. An alternative is to use the Activate/Deactivate events. If you do a search on the Excel and VB/VBA threads, you will find a lot more on this subject.

Reply | Quote

As Hans pointed out, the best way is to create the toolbar on the fly, and then destroy it when leaving the book. You can also use the following to trap the cancel and avoid deleting the toolbar until the user actually decides to close the book:

If Cancel = False Then Exit Sub

If you search the Lounge, you will get many examples of code for creating toolbars on the fly.

Reply | Quote