Programming Excel with VBA and .NET

Add Edit Controls to Toolbars

The best reason I can think of to create a toolbar in code is if you want to use edit controls, such as a drop-down list or combo box, within the toolbar. There’s no way to drag one of those types of controls onto a toolbar manually; you can create them only through code.

To create an edit control on a toolbar:

1. Get a reference to the toolbar.

2. Use the Controls collection’s Add method to create the control on the toolbar.

3. Set the control’s properties and populate any lists it contains.

The Add method creates different types of controls on a toolbar based on the Type argument as described in Table 19-1.

Table 19-1. MsoControlType constants for creating command bar controls

Type argument	Creates	Object type of control is
msoControlButton	Toolbar button or menu item	CommandBarButton
msoControlComboBox	A combo list box (select an item or enter text)	CommandBarComboBox
msoControlDropdown	A drop-down list box (select an item)	CommandBarComboBox
msoControlEdit	An edit box (enter text)	CommandBarComboBox
msoControlPopup	A menu of other items	CommandBarPopup

The middle three control types in Table 19-1 are edit controls. You can get or set the values of those controls from code. For example, Figure 19-15 shows a toolbar that lists all the macros in the current workbook in a drop-down list. You can select an item from the list and click Run to run the macro. The toolbar also includes some built-in commands that I often use.

Figure 19-15. A toolbar with ...