It should once again be noted that toolbars from earlier versions of Excel, if you still decide to create them, have several significant limitations.
- First, they cannot be freely positioned within the workspace of an open workbook.
- Second, they will always appear on the Add-Ins tab of the Ribbon.
- Third, many of the properties and methods of the CommandBar object, which encapsulates data about menus, context menus, or toolbars, may simply be ignored in Microsoft Office Excel 2010.
In addition, unlike Ribbon modifications, custom toolbars are available to all Excel workbooks.
Let us consider an example of creating a toolbar, which will contain several buttons that respectively:
- display a greeting,
- display the current date,
- launch Notepad.
Steps to Create the Toolbar
- In the ThisWorkbook module, enter two procedures: the first will create the toolbar when the workbook is opened, and the second will delete the toolbar when the workbook is closed.
Procedures for creating and deleting a toolbar in a workbook (ThisWorkbook module)
Private Sub Workbook_Open() Call CreateToolbar End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteToolbar End Sub
- In a standard VBA module (Module1), place the code for the procedure that creates the toolbar (when the workbook is opened). This includes the description of the required controls, the procedure for deleting the toolbar when the workbook is closed, as well as the procedures that handle events for the toolbar controls (in our case, clicking the respective toolbar buttons).
Note
We emphasize once again that custom toolbars will be available for all Excel workbooks. If custom toolbars load when opening a workbook, you can:
- disable the Add-Ins tab, and/or
- completely remove them.
To do this, in the ThisWorkbook module, place the DeleteToolbar procedure, for example, during workbook opening, and in a standard module provide the actual code of the DeleteToolbar procedure specifying which toolbars should be deleted.