Pages in this article
-
Preparations
-
Book or add-in
-
Menus
-
Toolbars
-
Limit access
-
Protect code
-
Initialise
-
Save Settings
-
UI Languages
-
Setup
-
Conclusion
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.