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.
- Customize Quick Access Toolbar Excel 2007
- Customize Toolbar Excel 2016
- Customize Mini Toolbar Excel
- Customize Quick Access Toolbar Excel
- How To Customize Toolbar Excel 2007
In order for the Excel button to show up on your toolbar you'll have to include the HTML for it inside the template. So just load the page once with the toolbar template commented out, see what HTML toolbar.Excel generates and then copy and paste that inside the template. From the Toolbar submenu select Customize. Choose the Commands tab. Select the name of the menu (I used Insert ) Scroll in the right window to find the button. Click and drag the button to the space on the toolbar where you want the button. Release the mouse and the button is placed. 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.
The code that follows creates a new toolbar (with two buttons) that is displayed in the Add-ins tab in the Custom Toolbars group of the Ribbon:
Sub CreateToolBar()
Dim cBar As CommandBar
Dim cControl As CommandBarControl
RemoveToolBar
' Create toolbar
Set cBar = Application.CommandBars.Add cBar.Name = 'ExcelVBADummies' cBar.Visible = True
' Add a control
Set cControl = cBar.Controls.Add With cControl
.OnAction = 'ToolButtonOpen_Click' .TooltipText = 'Excel VBA for Dummies Demo: Open' End With
' Add another control
Set cControl = cBar.Controls.Add With cControl
.OnAction = 'ToolButtonSave_Click' .TooltipText = 'Excel VBA for Dummies Demo: Save' End With End Sub
Customize Quick Access Toolbar Excel 2007
Notice that I set the FaceID property when I added a control. This property determines the icon on the button. The OnAction property specifies the macro to execute when the button is clicked. The TooltipText property contains text that is displayed when the user hovers the mouse pointer over the control. Figure 19-8 shows the result of running the code:
Figure 19-8:
The Add-ins tab of the Ribbon shows the result of running the CreateTool Bar routine.
if | rj ; | Boofc2 - Microsoft Exce | |||
y a | Home Insert Page Layout | Formulas Data Review View Developer Add-ins | |||
Cus | ;om Toolbar | ||||
N10 | t | ||||
: | A | B 1 C 1 D | E 1 F | G H 1 | 1 K |
2 | |||||
3 | |||||
Sub | RemoveToolBar() |
On Error Resume Next | |
Application.CommandBars('ExcelVBADummies'; | i .Delete |
End | Sub |
Customize Toolbar Excel 2016
And last but not least, here are the two procedures that respond to the button clicks:
Application.CommandBars.ExecuteMso Sub | 'FileOpen' |
Sub | 'FileSave' |
These two procedures execute the appropriate Ribbon commands.
This example is available on the book's Web site. You can also find a workbook that contains a list of all the Excel 2007 Ribbon commands.
Customize Mini Toolbar Excel
When you create a custom toolbar, it remains part of the UI until it is deleted. If the workbook that contains the macros is closed, that workbook is reopened if you click a button on the toolbar.
Part V
Customize Quick Access Toolbar Excel
Continue reading here: Creating Worksheet Functions and Living to Tell about It
How To Customize Toolbar Excel 2007
Was this article helpful?