Menus , Toolbars , and VBA As I mentioned earlier, there might be times when it's inconvenient, impractical , or downright impossible to make design-time modifications to a user's menus or toolbars. What do you do in these situations if you want to give the user pull-down menu or toolbar access to your VBA procedures? If you're building an Excel project, there is a way to distribute custom toolbars with your project: -
Activate the workbook to which you want to attach the custom toolbar. -
Select Tools, Customize and choose the Toolbars tab in the Customize dialog box. -
Highlight your custom toolbar and then click Attach. Excel displays the Attach Toolbars dialog box. -
Highlight the custom toolbar and then click Copy. -
Click OK. What do you do in other applications or if you don't want to attach a toolbar in Excel? Easy: Get your project to build its own menus, submenus, commands, and toolbars at runtime. For example, the document that contains your VBA project has an Open event that you can use to construct the necessary menu structure each time the user runs the application, as well as a Close event to remove the custom items when the project shuts down. The secret of controlling menus and toolbars programmatically is VBA's CommandBars object. This Microsoft Office object is a collection that represents all the command bars in the current application, where a "command bar" can be any of the following: a menu bar, a shortcut menu, or a toolbar. You can use the properties and methods of the CommandBars collection to make modifications to the application's menus and toolbars within procedures. This includes not only simulating the basic design-time techniques of adding menus, commands, and toolbars, but also some techniques that are only available at runtime, such as renaming, disabling, and enabling menus and commands. The rest of this chapter takes you on a brief tour of some of these techniques. |
