| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Excel lets you store command bars with an individual workbook or in the Excel workspace. Workspace command bars are saved in a file named Excel.xlb. If user profiles have been set up for multiple users, Excel.xlb is stored in the C:\Windows\Profiles\UserName\Application Data\Microsoft\Excel subfolder. If user profiles have not been set up, Excel.xlb is stored in the C:\Windows\Application Data\Microsoft\Excel subfolder.
You can copy command bars from the workspace to a workbook by using the Attach Toolbars dialog box (click Attach on the Toolbars tab of the Customize dialog box). You cannot copy command bars to a workbook by using VBA code. After you have copied a command bar to a workbook, you can delete it from the workspace by clicking Delete on the Toolbars tab of the Customize dialog box or by using the Delete method of the CommandBars collection.
Important When you open a workbook, Excel copies the workbook's custom command bars that do not already exist in the workspace to the workspace. These copied command bars are not deleted from the workspace when you close your workbook. If you want custom command bars to be available only when your workbook is open, you must programmatically delete them from the workspace when your workbook closes. When you delete a command bar in this fashion, you are removing only the workspace copy, not the workbook copy. The workbook copy will be again copied to the workspace the next time your custom application opens. If you do not delete the workspace copy and the workspace copy of the command bar is modified by the user, the workbook copy will not be recopied to the workspace when your workbook is reopened.
You can't use VBA to copy a workspace command bar to a workbook or to delete a workbook command bar from a workbook. The only way to delete a custom command bar from a workbook is to use the Delete button in the Attach Toolbars dialog box.
If a control on the workspace copy of the command bar calls code that exists in a workbook and the workbook is not open when the control is used, the workbook is immediately opened and made visible.
Command bars that you create to distribute with a custom application should be stored in the application's workbook or template.