JKP Application Development Services.

                    Microsoft Office Application Development

Creating an addin from an Excel macro.

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

Toolbars

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:

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.

Listing 2

(In the Thisworkbook module)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("YourToolBar").Delete
End Sub

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.

Listing 3

Option Explicit

Sub RemoveBar()
    On Error Resume Next
    Application.CommandBars("xlUtilDemo1").Delete
End Sub

Sub CreateBar()
    Dim oBar As CommandBar
    Dim oControl As CommandBarControl
    RemoveBar
    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
End Sub

Sub ButtonClicked()
    MsgBox "Your button works!!!"
End Sub

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:

Listing 4

Option Explicit

Private Sub Workbook_Open()
    CreateBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
End Sub
 

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:

Public bMeClosing As Boolean

And change the code in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    If Not bMeClosing Then
        Application.OnTime Now, "Createbar"
    End If
End Sub

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:

 Sub CloseMeNow()
    bMeClosing = True
    ThisWorkbook.Close
End Sub

Excel 2007

Toolbars have been stripped from Excel 2007. 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.

<<Previous        Next>>

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2008 JKP Application Development Services