Add Controls To A

You can add controls to a toolbar that correspond to VBA macro code or any other Excel commands that you want to execute when selecting that toolbar control. You can add controls to any toolbar available within Excel. You can add existing Excel controls to the toolbar, or you can add new controls. When you add a new control to a toolbar, you can specify the icon image to represent the control along with the tool tip text, which displays when you drag the cursor across the control. You add a new control by specifying the toolbar where you want to add the control followed by the Add method.

When you reference specific CommandBar objects within the CommandBars collection, you need to use the Controls property to return the collection of objects. When you use the Add method without any parameters, Excel places a blank control on the toolbar. See the section

TOOLBAR

"Working with CommandBars" for more information about the parameters available with the Add method. If you add an existing control to the toolbar, you can specify the ID of the control using the Id parameter.

When you add a control to a toolbar, you use the properties associated with the CommandBarControl Object to customize your control. If you did not assign an existing Excel command to the control, you need to indicate the action to perform when a user selects the control. You do this by specifying the name of the VBA procedure to run using the OnAction property.

To make the toolbar control easy to recognize, you must assign it a button image using the FaceID property. You can specify an image value between 0 and 3499.

ADD CONTROLS TO A TOOLBAR

ADD CONTROLS TO A TOOLBAR

□ Create a new subroutine.

0 Type Dim NewControl As CommandBarControl, replacing NewControl with toolbar button variable.

-Q Type Set CBar = CommandBars("MyToolbar"), replacing "MyToolbar" with the name of the toolbar.

0 Type Set NewControl = CBar.Controls.Add(Type: = imsoControlButton).

□ Create a new subroutine.

'-0 Type Dim CBar As CommandBar replacing CBar with the toolbar variable.

0 Type Dim NewControl As CommandBarControl, replacing NewControl with toolbar button variable.

-Q Type Set CBar = CommandBars("MyToolbar"), replacing "MyToolbar" with the name of the toolbar.

0 Type Set NewControl = CBar.Controls.Add(Type: = imsoControlButton).

0 Type With NewControl.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

TYPE THIS:

Sub GetControlID()

Dim RowId As Integer

Dim CB As CommandBar

Dim CBC As CommandBarControl

For Each CB In CommandBars

For Each CBC In CommandBars(CB.Name).Controls Cells(RowId, 2) = CBC.ID Cells(RowId, 3) = CBC.Caption RowId = RowId + 1

Next Next End Sub

RESULT:

The subroutine looks at each command bar in the CommandBars object and returns the name of the command bar along with a list of the control names and IDs on that particular command bar.

I Type End With.

L-E Type CBar.Visible = True.

Excel adds the button to the toolbar.

■ When you click the button, the corresponding macro executes.

□ Type .FaceId = 24, replacing 24 with the ID of the toolbar button image.

—O Type .OnAction = "MySub", replacing "MySub with the name of the macro to execute.

I Type End With.

L-E Type CBar.Visible = True.

— Switch to Excel and run the macro.

■ Excel adds the button to the toolbar.

■ When you click the button, the corresponding macro executes.

0 0

Post a comment