Option Explicit
Sub CreateMyTool()
'Makes a toolbar called "cbMyTool"
Dim cbMyTool As CommandBar
Dim cbbMyButton As CommandBarButton
'Make the toolbar
Set cbMyTool = CommandBars.Add
'Now we add a button to the toolbar. FaceId is the button's icon,
'OnAction is the macro to run, if the button is clicked, and
'ToolTipText is the text that will show when the mouse hovers.
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro1"
.FaceId = 645
.TooltipText = "Do magic with numbers"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro2"
.FaceId = 940
.TooltipText = "Show a message box"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro3"
.FaceId = 385
.TooltipText = "Functions"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro1"
.FaceId = 1662
.TooltipText = "Constraints"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro2"
.FaceId = 225
.TooltipText = "Lock cells"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro3"
.FaceId = 154
.TooltipText = "Delete all"
End With
'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
.OnAction = "DummyMacro1"
.FaceId = 155
.TooltipText = "Return"
End With
'Before we finish, the toolbar gets a name, width and
'is put on the screen.
With cbMyTool
.Name = "Shortcuts"
.Left = Application.ActiveWindow.Width
.Top = Application.ActiveWindow.Height
.Visible = True
.Width = 300
End With
BeforeExit:
Set cbMyTool = Nothing
Set cbbMyButton = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description & " CreateMyTool", vbOKOnly + vbCritical, "Error"
Resume BeforeExit
End Sub
Sub DeleteMyTool()
'Removes the toolbar "Shortcuts".
'If it doesn't exist we get an error,
'and that is why we use On Error Resume Next.
On Error Resume Next
CommandBars("Shortcuts").Delete
On Error GoTo 0
End Sub
Sub RemoveToolBar()
'Removes all not-built-in menus and
'toolbars and restores Excel's standard
'menu. This is an alternative to
'DeleteMyTool, that only removes
'the named toolbar Shortcuts.
Dim cbBar As CommandBar
On Error GoTo ErrorHandle
For Each cbBar In Application.CommandBars
If Not cbBar.BuiltIn Then cbBar.Delete
Next
Exit Sub
ErrorHandle:
MsgBox Err.Description & " RemoveMenu", vbOKOnly, "Error"
End Sub
