The add-in manager calls the Auto_Add and Auto_Remove routines when installing or removing the add-in, allowing code to perform setup or cleanup tasks. As an example that further illustrates working with toolbars, the code in Example 9 below sets up a toolbar manually. Set the Name property of the custom toolbar when creating it with the syntax Application.Toolbars.Add Name:="Your Toolbar Name". Trying to set the toolbar's Name property using a separate line of code generates an error that a toolbar does not have a writeable name property. This can be confusing because Help says the Name property is writeable for custom toolbars.
Each new button added to the toolbar requires an ID in order for the Add method to work. Any ID will do since the code will modify the button properties after creating it. To insert a blank Custom button, use ID 231. A helpful trick is to store the custom button bitmaps on one of the dialog sheets in the add-in workbook. Just place them off to the side of the dialog and they will be invisible, but the code will be able to copy them to the toolbar button's face. This trick eliminates the need to store button images in separate files.
'Toolbar constants
Const TBNAME = "Custom Charts"
Const CUSTOMBTNID = 231
Sub Auto_Add()
'This routine is called from the add-in manager when the add-in
' is loaded.
Dim Tbar As Object
Dim TbarExists As Boolean
TbarExists = False
'Scan installed toolbars to see if the one for the Histogram Wizard
' is installed.
For Each Tbar In Application.Toolbars
If Tbar.Name = TBNAME Then
TbarExists = True
End If
Next Tbar
'Install the toolbar if necessary.
If Not TbarExists Then
Application.Toolbars.Add Name:=TBNAME
Application.Toolbars(TBNAME).Visible = True
'Copy the button face picture (stored in the Wiz1 dialog).
' Must copy as xlBitmap format for paste operation to work!
ThisWorkbook.DialogSheets("Wiz1").[HWButtonFace].CopyPicture _ format:=xlBitmap
'When accessing buttons by item number, remember that a gap
' counts as an item!
'Add a button to the toolbar and define properties...
Application.Toolbars(TBNAME).ToolbarButtons.Add (CUSTOMBTNID)
With Application.Toolbars(TBNAME).ToolbarButtons(1)
.PasteFace
.Name = "Histogram"
.OnAction = "HistoMain"
End With
End If
End Sub
Sub Auto_Remove()
'This routine is called from the add-in manager when the add-in
' is removed.
On Error Resume Next
Application.Toolbars(TBNAME).Delete
On Error GoTo 0
End Sub
Example 9. Auto_Add and Auto_Remove routines