Advantages of a Custom Toolbar

Removed in 2007
The toolbar will only appear when that specific workbook is opened.
A custom toolbar like all other toolbars can be repositioned and docked anywhere on the screen.
You can add as many custom toolbars to a workbook as you like.
Attaching a toolbar to a workbook can make it easier to distribute.
Any toolbars that have been attached to a workbook will automatically appear when that workbook is opened.


Attaching a Toolbar to a Workbook

It is possible to attach any number of custom toolbars to a workbook.
Lets assume that you have created a custom toolbar called "Custom 1" that contains a few useful commands.
Select (Tools > Customise) and select the "Toolbars" tab.
Select the toolbar which you would like to attach, in this case "Custom 1".
Press the "Copy" button to transfer it to the active workbook. Press OK.


Removing an attached toolbar.

The "Copy" button will automatically change to a "Delete" button if you select a toolbar that is attached to a workbook.

Always delete custom toolbars that you do not use. This can be done from the (View > Toolbars > Customise) dialog box.


Disadvantages of attaching a Custom Toolbar

If you make any changes to an attached toolbar (such as adding or removing command buttons) you must detach the toolbar, make your changes, and then reattach it.
The first time a user opens a workbook with a custom toolbar, the correct toolbar will be displayed.
When the user closes Excel this custom toolbar will be saved to their (".xlb") file (i.e. the default Excel workspace) and will therefore always appear when Excel is open.
Remember that if you want a custom toolbar to only be available in a particular workbook or to a workbook based on a specific template, you must create (and delete) the toolbar each time the workbook is opened and closed.


Create your Toolbars on the fly

We do not recommend that you attach your toolbars to any workbooks (or templates) and always recommend you "create them on the fly".
Instead always create your toolbars when the workbook is opened and then delete them when the workbook is closed.
You can create (and delete) your toolbars automatically by using the Workbook_Open and Workbook_BeforeClose events.
If you are creating an Excel add-in, then your toolbars should be created when the add-in is installed and deleted when the add-in is uninstalled.
When your toolbars are created "on the fly" they do not have to be immediately visible. You can create the toolbars and just set their visible property to False.
This way the custom toolbar is never saved to the ".xlb" file.


Toolbars attached to workbooks are not removed.
If you open a workbook that has a custom toolbar attached it will be displayed.
If you close the workbook the toolbar is not removed.


Excel will not replace an existing toolbar in the Attach toolbars dialog box.
If you have a workbook that contains an older copy of a toolbar this will have to be deleted before you can copy a new one
Test Functionality


Removing an Attached Toolbar

If you have attached a custom toolbar to a workbook, there can be problems if you send a new version of the toolbar attached to a workbook or the user saves the workbook under a different name.
The old toolbar is not replaced when you open the new workbook, and the macros that the old toolbar runs are still in the old workbook
It is always best to delete the custom toolbar when you close the workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
   Application.CommandBars("MyToolbar").Delete
End Sub

Important

A custom toolbar attached to a workbook is not displayed if a toolbar with that name already exists.
If there is more than one toolbar with the same name, the copy that is part of the default Excel workspace (i.e. saved in the ".xlb" file) will be the only one displayed.
Only custom toolbars can be attached to workbooks.
Once you have removed an attached toolbar there is no way to get it back.
You cannot remove any of the standard toolbars.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrev