To move a toolbar from the workspace to a workbookBesides making changes to the system of toolbars, you can control the scope of the changes by choosing whether to store them with the workspace or with a workbook.
Custom toolbars can be stored with the workspace or with the workbook. When you quit Microsoft Excel, the toolbars in the workspace are saved in the file Username.xlb (where Username is the Windows 95 logon name of the current user). If the user isn't logged on, the filename is Excel5.xlb. The toolbars saved in a workbook are stored in the workbook file.
Workbook-level toolbars make it easier to create a polished user interface for a custom application, such as an add-in, and to distribute custom toolbar buttons and their supporting procedures. If you're going to distribute a custom toolbar with a custom application, you should attach it to the workbook containing the application so that the toolbar is stored in the same file as the application.
To move a toolbar from the workspace to a workbook
1. While a Visual Basic module is active, click Attach Toolbars on the Tools menu.
2. In the Custom Toolbars box, click the name of the toolbar you want to copy to the active workbook.
3. Click Copy.
The name of the toolbar you copied appears in the Toolbars In Workbook box.
You can delete the original workspace-level toolbar by clicking Toolbars on the View menu, selecting the check box next to the name of the toolbar you want to delete, and then clicking Delete. If you don't delete the workspace version of the toolbar, you can change it without affecting the version stored in the workbook. If you make changes to the workspace version of the toolbar and would like to update the workbook version to match it, you can copy the current workspace version of the toolbar to the workbook again, replacing the previous workbook version.
After you've copied a toolbar to a workbook, the toolbar becomes available only after the user has opened that workbook. A workbook toolbar retains not only the name and contents of the toolbar, but also the assignment of code to toolbar buttons; the location, size, and shape of the toolbar; its on-screen position; and whether it's visible or hidden.
You can also delete a workbook toolbar.
To delete a workbook toolbar
1. While a Visual Basic module is active, click Attach Toolbars on the Tools menu.
2. In the Toolbars In Workbook box, click the name of the toolbar you want to delete.
3. Click Delete.
When you open a workbook that contains one or more toolbars, Microsoft Excel first determines whether a workspace toolbar with that name already exists. If not, Microsoft Excel creates a new workspace toolbar and copies the workbook toolbar into it. This way, the you get a fresh copy of the toolbar that you can alter by hiding it or by copying toolbar buttons to or from the workspace-level copy of the toolbar. When you quit Microsoft Excel, changes made to this copy of the toolbar are stored with the workspace file.
There is no way to rename the toolbar, so when the workbook or add-in is reopened, the workspace already contains a toolbar with the same name as the workbook toolbar, and Microsoft Excel uses the workspace copy rather than reloading the workbook toolbar. However, the procedures that support the toolbar buttons in the open workbook still run when the user clicks the corresponding toolbar button.
As a developer, you can design a toolbar and then attach it to an add-in workbook, as shown in the preceding section. When the user opens the add-in workbook, the custom add-in toolbar appears. The user can then edit it and move toolbar buttons from it to personal toolbars, without affecting the copy stored in the add-in workbook. The user's changed toolbars are stored with the workspace file when he or she quits Microsoft Excel. When the user starts Microsoft Excel again, the edited toolbar appears; clicking one of the developer's toolbar buttons loads the add-in workbook containing the procedure attached to that toolbar button. To generate a fresh copy of the workbook toolbar, the user can delete the edited copy.