Creating an addin from an Excel macro

Pages in this article

Toolbars

A toolbar can be a very convenient way to give users access to your macros.

Creating a toolbar and adding a button to it is easy enough, see 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".

Note that this code creates a toolbar whos buttons will appear in the add-ins tab of Excel's ribbon.

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_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
End Sub

Private Sub Workbook_Open()
    CreateBar
    MakeMenu
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
    RemoveMenu
    If Not bMeClosing Then
        Application.OnTime Now, "Createbar"
        Application.OnTime Now, "MakeMenu"
    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 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.