Create A Custom Toolbar

You can design a VBA procedure to create new toolbars within Excel where you can place links to the custom macros you create. You create a new toolbar by adding a new CommandBar object to the CommandBars object collection. Excel comes with approximately 30 different built-in toolbars, to which you can add controls. By creating new toolbars to house your custom toolbar options, you do not affect the layout of the standard toolbars.

of True for the MenuBar parameter. If you only want the toolbar to display after running the associated procedure, set the value of the Temporary parameter to True. If you create a temporary toolbar it is deleted when you close Excel. To lauch the toolbar again, you need to rerun the associated procedure. See the section "Working with CommandBars" for more information about the Add method properties.

You can create a new toolbar by using the Add method associated with the CommandBars collection. Although the Add method has four different parameters, they are all optional. You should specify a name for the new toolbar using the Name parameter. Also, indicate the location where you want to place the toolbar in the window using the Position parameter values. If you want the CommandBar you create to replace the current menu bar, specify a value

Adding a new toolbar with the Add method creates a new blank toolbar that is not visible in Excel. To make it visible, you set the Visible property associated with the toolbar to True. In fact, you can use this property at any time to switch between having a visible and invisible toolbar.

After you create the toolbar, you need to use the various properties associated with the CommandBar object to customize the location, protection, size, and visibilty.

CREATE A CUSTOM TOOLBAR

CREATE A CUSTOM TOOLBAR

'—n Create a new subroutine.

H Type Dim NewTBar As CommandBar, replacing NewTBar with the name of the toolbar you want to create.

-H Type Set NewTBar : CommandBars.Add (Temporary:=True).

'—n Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

H Type Dim NewTBar As CommandBar, replacing NewTBar with the name of the toolbar you want to create.

-H Type Set NewTBar : CommandBars.Add (Temporary:=True).

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

ffiyi fS If you attempt to create a custom toolbar with the same name as an existing toolbar, Excel returns an error message. To avoid these errors, you can add code to you procedure that checks for the existence of a CommandBar object with the same Name parameter as your new one. If a toolbar exists, you can avoid the attempt to add or add the toolbar with a different name. The following code checks to see if a toolbar named "MyToolbar" exists.

Example:

For Each CB In CommandBars

If CB.Name = "MyToolbar" Then

TBFound = "True" End If

Next

This code uses the For Each Next statement to cycle through each CommandBar object in the CommandBars collection to determine if any of the existing CommandBar objects have a Name property value of "MyToolbar". If Excel encounters a match, the TBFound variable is set to a value of True. If you see that the value of the TBFound variable is False, you can add the new toolbar. See Chapter 6 for more information on working with For Each Next statements.

L-Q Type End With.

• Switch to Excel and run the macro.

Excel creates the new toolbar and displays it on the window.

S Type .Name = "MyToolbar", replacing "MyToolbar" with the name of the new toolbar.

—0 Type .Position = msoBarFloating, replacing msoBarFloating with the MsoControlType constant value.

L-Q Type End With.

Note: See Chapter 4 for more information on using the End statement with objects.

• Switch to Excel and run the macro.

■ Excel creates the new toolbar and displays it on the window.

With

0 0

Post a comment