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.
- Right-click anywhere on the QAT, and click 'Customize Quick Access Toolbar'. At the bottom right, click the Import/Export button. Click Export all customizations. Leave the default file name as is, or change the file name, then click Save. To import your saved Ribbon and QAT customizations, follow these steps.
- Custom toolbars are not as versatile in Excel 2007 as they are in Excel 2003 and before because they always appear in the Add-InsOCustom Toolbars tab (and they can't be moved). But custom toolbars are still a viable option if you're willing to put up with the limitations. For this reason, I include a simple example of creating a custom toolbar.
-H Type Set NewTBar : CommandBars.Add (Temporary:=True).
'—n Create a new subroutine.
Note: See Chapter 3 for information on creating subroutines.
To attach the custom toolbar and over-come these issues, follow the steps below; 1) Open the Workbook that should have the custom toolbar. 2) Right click on any grey unused part of any toolbar and choose 'Customize'. 3) On the 'Toolbars' page check 'MyToolbar' (or the applicable name) so it becomes visible. Right-click on the Quick Access Toolbar. Choose Customize the Quick Access Toolbar. Under Choose Commands from, select Separator at the top of the list. Click Add to add to the toolbar.
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.
• 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.
Where Is Customize Toolbar Excel
—0 Type .Position = msoBarFloating, replacing msoBarFloating with the MsoControlType constant value.
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.
Customize Excel Toolbar Mac
With
Continue reading here: Add Controls To A
Customize Quick Access Toolbar Excel
Was this article helpful?