Working with Command Bar Controls

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

An empty toolbar does not serve any purpose. To make the toolbar useful, you need to place on it the desired controls and assign to them appropriate VBA procedures. There are three types of command bar controls, as shown in the following table. Table 12-3: Types of controls that can be placed on toolbars Object Name Description

CommandBarButton This object represents toolbar buttons and menu options.

When you click a button or select a menu option, an appropriate VBA procedure is executed.

Object Name Description

CommandBarPopup This object represents pop-up controls that display a menu or submenu when clicked.

CommandBarComboBox This object represents text boxes, list boxes, or combo boxes (for example, the Font and Font Size controls on the Formatting toolbar or the Zoom control on the Standard toolbar).

One of the important properties of the CommandBar object is the Controls property. This property returns the collection of all the controls on a specific toolbar.

1. Try out the following statement in the Immediate window: ?CommandBars(1).Controls. Count

When you press Enter, Visual Basic returns the total number of controls available in the worksheet's menu bar.

2. Enter the following statement to return the name of the first control in the worksheet's menu bar:


Visual Basic returns the name of the first control: &File. The & character in front of the letter F indicates that this menu option can be executed from the keyboard by pressing Alt+F.

3. Enter this statement to execute a specific option: CommandBars(1).Controls(1).Execute

The Execute method activates the specified control. The File menu should open up.

4. In a Code window of the current project, enter the following ControlList procedure to write to the Immediate window the names of all the controls on the active menu bar:

Sub ControlList()

Dim bar As CommandBar

Dim ctrl As CommandBarControl

Set bar = CommandBars(1)

Debug.Print bar.Name & ": " & bar.Controls.Count For Each ctrl In bar.Controls Debug.Print ctrl.Caption

Next End Sub

5. Check the Immediate window after you have run the above procedure. You should see the following list:

Worksheet Menu Bar: 10











Adding Controls to a CommandBar

To run a desired VBA procedure, you can add a built-in or custom control to the built-in toolbar. If you prefer, you can add a control to a custom toolbar. Whether you add a built-in or a custom control to a built-in or custom toolbar, always use the Add method with the following syntax:

CommandBar.Controls.Add(Type, Id, Parameter, Before, Temporary)

CommandBar is the object to which you want to add a control.

Type is a constant that determines the type of custom control you want to add. You may select one of the following types:

msoControlButton 1

msoControlPopup 10

msoControlEdit 2

msoControlDropDown 3

msoControlComboBox 4

Id is an integer that specifies the number of the built-in control you want to add.

Parameter is used to send information to a Visual Basic procedure or store information about the control.

The Before argument is the index number of the control before which the new control will be added. If omitted, Visual Basic adds the control at the end of the specified command bar.

The Temporary argument is a logical value (True or False) that determines when the control will be deleted. Setting this argument to True causes the control to be automatically deleted when the application is closed.

1. Enter the AddBarAndControls procedure in the Code window, as shown below:

Sub AddBarAndControls( )

With Application.CommandBars.Add("Test", , False, True) .Visible = True .Position = msoBarBottom

With .Controls.Add(msoControlButton) .Caption = "List of Controls" .FaceId = 4

.OnAction = "ControlList" End With End With End Sub

This procedure creates a new toolbar named Test and places it at the bottom of the application window. Next, the Add method places a button on it named List of Controls identified by the printer icon. When the user clicks the button, the ControlList procedure that was prepared earlier will be executed.

Understanding and Using Control Properties

Controls placed on toolbars have many properties that you can read or set. To find out whether a control is built-in or custom, use the BuiltIn property. If the returned value is True, the control in question is built in. All user-defined controls return the value of False. If the value of the Enabled property is True, the specific control is active and can respond to a mouse click. An inactive control has its Enabled property set to False. It goes without saying that all controls have the Caption property that can be used to find out or set the control's title.

The combo type controls represented by the CommandBarComboBox object have specific properties, such as DropDownLines, DropDownWidth, List, ListCount, ListIndex, and Text. The explanation of these properties appears in Table 12-4. Table 12-4: Selected properties of the CommandBarComboBox object



DropDownLines Returns or sets the number of items that appear when the user clicks the drop-down arrow in the combo box.

DropDownWidth Returns or sets the width of the combo box control in pixels.

List(Index) Returns or sets the value of the list item given by Index (the index of the first item in the list equals zero (0)).

ListCount Returns the number of items in the list.

ListIndex Returns or sets the selected item in the list.

Text Returns or sets the text that appears in the text box part of the combo box control.

1. Enter the MyCombo procedure in the Code window, as shown below:

Sub MyCombo()

Dim cbo As CommandBarControl

Set cbo = CommandBars(4).Controls.Add(Type:=4, Before:=1) With cbo

.AddItem Text:="Row", Index:=1 .AddItem Text:="Column", Index:=2 .Caption = "Insert Row/Column" .DropDownLines = 2 .DropDownWidth = 80 End With End Sub

The MyCombo procedure creates a combo box control (Type: =4 indicates msoControlComboBox ) and places it at the very beginning of the built-in Formatting toolbar (this toolbar is the fourth CommandBar object in the CommandBars collection). Next, two items are added to the combo box control. The procedure also sets the caption and the width of the combo box control.

2. Switch to the Microsoft Excel application window and examine the first control on the Formatting toolbar.

3. Return to the Visual Basic Editor window.

4. Type the following statement in the Immediate window to remove the combo box control from the Formatting toolbar added by the MyCombo procedure:


When you press Enter, Visual Basic deletes the first control on the Formatting toolbar.

The buttons that appear on toolbars are easily recognized thanks to the images placed on them. If a control on a toolbar is a CommandBarButton object, the FaceId property returns or sets the ID number of the icon on the button's face. The icon ID number (FaceId), in most cases, is the same as the control's Id property. The icon image can be copied to the Windows clipboard using the CopyFace method.




. 0





















&Mail Recipient








Print (Lexmark Optra M412)




Print Pre&view



















&Format Painter


















Sort ^Ascending





Sort Des&cending




&Chart Wizard




&D rawing







Microsoft Excel &Help



14 1


Ni\5hMtL/'9lMl&/$Mt3 /

Figure 12-3: List of icons on the Standard toolbar. You can modify the Images procedure to get the complete listing of buttons and their icons on any toolbar.

The following Images procedure writes a list of buttons that appear on the Standard toolbar to a spreadsheet. In addition to the button name, the list shows its icon. Because one cannot copy the image of the icon that is currently disabled (see the Undo and Redo buttons on the Standard toolbar), when Visual Basic attempts to copy the button's face to the clipboard, it encounters an error. The Images procedure traps this error with the On Error GoTo ErrorHandler statement. This way, when Visual Basic encounters the error, it will jump to the ErrorHandler: label and execute the instructions below this label. The last statement, Resume Next, will send Visual Basic to the instruction below the one that caused the error, and the procedure will continue until all the buttons on the Standard toolbar have been checked out. You will learn more about error trapping in the next chapter.

Sub Images()

Dim i As Integer

Dim total As Integer

Dim buttonId As Integer

Dim buttonName As String

Dim myControl As CommandBarControl

Dim bar As CommandBar

On Error GoTo ErrorHandler

Workbooks.Add Range("A1").Select With ActiveCell

.Value = "Image" .Offsets, 1) = "Index" .Offset^, 2) = "Name" .Offset^, 3) = "FaceId" End With

Set bar = CommandBars(3) total = bar.Controls.Count With bar

For i = 1 To total buttonName = .Controls(i).Caption buttonId = .Controls(i).ID

Set myControl = CommandBars.FindControl(ID:=buttonId)

myControl.CopyFace ' error could occur here ActiveCell.Offset(1, 0).Select ActiveSheet.Paste With ActiveCell

.Offset^, 1).Value = buttonId .Offset^, 2).Value = buttonName .Offset(0, 3).Value = myControl.FaceId End With Next i

Columns("C:C").EntireColumn.AutoFit Exit Sub ErrorHandler:

Set myControl = CommandBars(3).Controls.Add With myControl

.FaceId = buttonId .CopyFace .Delete (False) End With

Resume Next End With

End Sub

Control Methods

Controls have methods associated with them. These methods allow you to perform such tasks as moving, copying, and deleting controls. Suppose that you want to copy the Bold button from the Formatting toolbar to the Standard toolbar.

1. Enter the following three statements in the Immediate window:

set myBar = CommandBars(3)

set myControl = CommandBars(4).Controls(3)

myControl.Copy Bar:=myBar, Before:=1

2. Switch to the Microsoft Excel application window. You should see the Bold button to the left of the New button on the Standard toolbar.

3. Switch back to the Visual Basic Editor screen, and enter the following statement in the Immediate window to remove the Bold button from the Standard toolbar:


By replacing the Copy method with the Move method, you can move the Bold button from the Formatting toolbar to the Standard toolbar. Try this on your own. To return the toolbars to the default state, use the Reset method. When you're done practicing moving and copying buttons, type the following statements in the Immediate window:

CommandBars(3).Reset CommandBars(4).Reset

If a control is a combo box (CommandBarComboBox), you can use the AddItem method to add a new item to the drop-down list. To remove an item from the list, use the RemoveItem method. Let's spend a few minutes and practice using these methods in the Immediate window.

1. Activate the MyCombo procedure that you prepared earlier. Run this procedure to place a custom combo box control on the Formatting toolbar.

2. Now enter the following statements in the Immediate window:

set myBar = CommandBars(4)

set myControl = CommandBars(4).Controls(1)


myControl.AddItem "Cells", 1

3. Switch to the Microsoft Excel application window and check the items that are available in the custom combo box control on the Formatting toolbar.

4. Return to the Visual Basic Editor window and reset the Formatting toolbar by typing in the Immediate window the following statement and pressing Enter:


Was this article helpful?

+1 -2


  • david
    How to find and delete commandbar controls containing specific text vba?
    11 months ago

Post a comment