Leading the way in Microsoft Office Development

Advantages of a Custom Toolbar

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.

 (View > Customise)(Toolbars tab, Attach) dialog box

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.

Things to Remember

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.