VBA Editor Customization: Menus, Toolbars and Toolbox

In the Visual Tour of Excel’s Coding Dojo series, we delved into the VBA editor’s menus, toolbars, and windows. If you found the glut of tools daunting, you’re not alone. Indeed, I did too. But, you’ll develop preferences that suit your workflow soon enough. Fortunately, the editor robustly supports customization. In fact, many of these ease the adoption of coding best practices. In this article, we explore the Excel VBA editor customization for menu, toolbar and userform toolbox.

Before we get started, there is something you should know. No, I haven’t perpetrated some heinous deed – not that I recall, at least. Note that any VBA editor customization done in Excel affects other Office applications on your computer.

On a side note, this article is one in a series of three on VBA editor customization. Listed below, for easy reference, are links to the other posts in the series:

• Personalizing the VBA Editor using the Options Dialog Box
• Customizing Projects using the Project Properties Dialog Box

Excel VBA Editor Customization – Menus and Toolbars

Menu and toolbar customizations let you select the toolbars you wish to make available by default. Also, they allow the addition of more commands to menus from a built-in list of commands. Moreover, you can resize toolbar icons and show or configure their ScreenTips.

The images below illustrate how you can access this VBA Editor customization. Further, these images reveal several other choices available to you. So, how do you get a feel for what they each do? You guessed it, get handsy with them and try each one out.

Excel VBA Editor Customization – UserForm Toolbox

The Toolbox is a windowpane housing all available UserForm controls. The Toolbox pops-up the first time you add a control to a UserForm.

Controls are visible and insertable VBA objects used in UserForm design. Moreover, they enable users to interact with programs by selecting or clicking on graphical elements directly.

If the Toolbox is hidden while a UserForm is active, you can use the View menu or Standard toolbar to show it. Moreover, you can enter Alt + V + X to unhide the Toolbox. Furthermore, the image below illustrates how to do this.

It is always a good idea to boost the ease of accessing controls. Besides, improved UserForm design efficiency is a fantastic side-effect of doing so. Customizing the Toolbox in any of the following ways does that:

• Customize control icons and tooltips (i.e., descriptive text);
• Insert customized versions of standard controls from UserForms;
• Include more controls using the Additional Controls command;
• Add, tailor, remove, import or export pages, and move controls between pages;
• Import or download advanced controls from external sources to expand UserForm capabilities.

To customize a control’s icon and tooltip

• Right-click the icon and select Customize from the pop-up context menu;
• Next, edit the ToolTip Text or the Tool icon;
• To edit the icon, click the Edit Picture or Load Picture buttons. These allow the insertion of a new bitmap image, which must be of the same size as the current icon or smaller.

To add customized standard controls to the Toolbox

• Add the standard control to a UserForm;
• Edit the standard control’s reusable properties in the Properties window;
• Use a simple drag and drop to copy the customized control to the Toolbox;
• Note that you can delete the new control from the Toolbox. You do this using the context menu displayed after right-clicking on it;
• Note that only properties get copied to the Toolbox, along with the new control. All the original control’s code must be manually copied to the new control or rewritten for it.

Add more controls using the Additional Controls command

Extending UserForm capabilities often means importing or downloading external controls. To do that, you use the Additional Controls command to add more controls to the Toolbox by:

• Opening the Additional Controls dialog box by selecting Additional Controls command;
• Choose the new controls from the Available Controls list shown in the dialog and click OK.

You can access the Additional Controls command from either the Tools menu. Moreover, you can also access the command from the pop-up context menu shown by right-clicking on any Toolbox icon. Also, right-clicking any Toolbox area below the title bar and tabs pops-up the context menu too. Furthermore, you can enter “Alt + T + A” to activate this command.

To add, remove, tailor, move, import or export a Toolbox page

• Right-click on the target page’s tab to display the context menu shown in the image below;
• Now, select your desired command – New Page, Delete Page, Rename, Move, Import Page, or Export Page;
• Note that exporting transfers a copy of the page, while retaining the source Toolbox page.

The Rename command displays the Rename dialog box, which lets you rename a Toolbox page and assign/edit its Control Tip Text.

The Move command displays the Page Order dialog box, which lets you reorder Toolbox pages.

To move controls between Toolbox pages

• Click and Hold the control’s icon in the source page, then drag it to hover on the destination page’s tab;
• As you do that, the Toolbox’s display switches to the destination page while hovering;
• Now, continue dragging the control’s icon onto the destination page’s main area;
• Once in the desired spot on the destination page, release the Hold to finish.

The Long and Short of It

Like most developers, you’ll want to set up your VBA editor in a manner that best suits your style or workflow. Excel supports a plethora of VBA Editor customizations to help you do just that. In this article, you’ve explored the Excel VBA editor customizations for menus, toolbars, and the userform toolbox.

This article is one in a series of three on VBA editor customization. Listed below, for easy reference, are the links to the other posts in the series:

• Personalizing the VBA Editor using the Options Dialog Box
• Customizing Projects using the Project Properties Dialog Box

Now, before you run off to make any alterations, remember that all VBA editor customizations in Excel also apply to the editors of all other Office applications on your computer.