A toolbar can be a very convenient way to give users access to your macros.
Excel 97 to 2003
Manually Create a Custom Toolbar
Creating a toolbar can be done manually by the menu command View, Toolbars, Customize. On the tab called "Toolbars" one can click the button "New..." to create a new custom toolbar. Excel will ask for a name for the new toolbar and subsequently present an empty toolbar. Watch out: this new toolbar often is hidden by the toolbar customization dialog itself, so this might have to be dragged out of the way to get the new toolbar into view.
Although custom toolbars are also saved to your local xlb file (as discussed in the previous chapter), there is one important difference between a menubar and a toolbar: one can attach a custom toolbar to a workbook.
This is how that works:
- Right click the toolbar and choose "Customize...".
- Click the "Attach..." button, located on the Toolbars tab
- Should the workbook already contain an identically named toolbar, then this one needs to be removed first by selecting it on the right-hand side and clicking delete.
- Select the new toolbar on the left-hand side of the dialog (see figure below) and click "Copy". Make sure your toolbar is completely finished before doing this though.
The Attach Toolbars dialog screen.
Note that any changes that are made to a custom toolbar are updated to the system version of the toolbar, not to the attached version. So when changes have been made, the attaching process needs to be repeated.
So what exactly happens when a workbook with an attached toolbar is opened?
Excel checks the user's .xlb file to see whether the toolbar is already on the system. If so, nothing is done and the local copy of the toolbar is used. If not, then Excel copies the toolbar from the workbook into the user's toolbar customization file. This means, that when one needs to be sure the user gets the most recent copy of a toolbar, the toolbar has to be removed from the system at closing of the workbook. That way, at every load of the workbook, the workbook toolbar will be used.
When the file containing the toolbar is saved as a new file, Excel will automatically update the references in the system toolbar to the NEW workbook. This can have very confusing effects: When one loads an older copy of the workbook and clicks one of the toolbar buttons, Excel will try to load the latest copy of the workbook, since that is what the toolbar's buttons will refer to. This confusion can only be avoided by making sure the toolbar is deleted from the system when the workbook is closed. Listing 2 shows the relevant code.
(In the Thisworkbook module)
On Error Resume Next
Of course opening two workbooks with this toolbar will have the unwanted effect, that the toolbar will only refer the macros in the first workbook. Closing either workbook will then remove the toolbar from the system, leaving a workbook without a steering wheel. Closing and reopening the file of course brings back the toolbar.
Situations like these arise when one has multiple copies of a file that contains macros to control the file. Such a situation calls for creation of an add-in that contains the macros, which are then separated from the multiple copies of the workbook. Maintenance of the macros will be much easier in such a case.
Create a Custom Toolbar programmatically
Creating a toolbar and adding a button to it is shown in the code in listing 3.
On Error Resume Next
Dim oBar As CommandBar
Dim oControl As CommandBarControl
Set oBar = Application.CommandBars.Add
oBar.Name = "xlUtilDemo1"
oBar.Visible = True
Set oControl = oBar.Controls.Add(ID:=1, Before:=1)
oControl.OnAction = "ButtonClicked"
oControl.FaceId = 275
oControl.Caption = "Click me!"
Set oControl = Nothing
Set oBar = Nothing
MsgBox "Your button works!!!"
The Sub Createbar creates one custom toolbar with one button, attached to the sub "ButtonClicked".
To make sure the toolbar is created at workbook open and destroyed at closure, the thisworkbook module should contain the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_Open()
There is a disadvantage to this method. When the user decides to cancel a shutdown of Excel (or closing the workbook), the Workbook_BeforeClose event code has already run, so the toolbar is destroyed, even though Excel hasn't been closed. To make sure the toolbar returns after such a cancellation, the code needs some modification.
1. Add a public variable to a normal module:
And change the code in the ThisWorkbook module:
If Not bMeClosing Then
Application.OnTime Now, "Createbar"
Application.OnTime Now, "MakeMenu"
So how does this work?
When the user closes Excel himself, the variable "bMeClosing" will be False and an OnTime event is scheduled which will run the CreateBar subroutine immediately after Excel has finished handling all events it needs to handle during its closing routine, including saving unsaved workbooks, etcetera.
If the user decides to cancel the closing operation, then the OnTime event will fire and the toolbar gets rebuilt. If however the user does not cancel the closure, nothing happens, because Excel has shut down.
A special situation arises when only the workbook with the code is to be closed (e.g. when an add-in is uninstalled). Then the Workbook_BeforeClose code runs, sets the OnTime event and the workbook closes. But immediately Excel will load the workbook again to process the scheduled OnTime event. It will even show the enable macros dialog again.
To shut down the utility itself, one needs to change the value of bMeClosing to true and subsequently close. In any normal module:
bMeClosing = True
Excel 2007 and up
Toolbars have been stripped from Excel 2007 and up. Any custom toolbars you create will appear as a separate group on the add-ins tab. If you don't like this, the only alternative you have is to do ribbon customisation (see previous page) and add your controls there.