Microsoft Office Tutorials and References
In Depth Information
10.3.2 Setting Up the Custom Menus
The first step is to create a new workbook that will act as the source for the add-in. Please do this
now. This workbook will eventually be saved as an add-in. I will refer to the workbook as
SRXUtils.xls , but you can feel free to name your version anything you like.
Incidentally, as we make changes to our add-in, we will do so in the SRXUtils.xls worksheet and
then save that worksheet over the current add-in. Before doing so, of course, we must unload the
current version of the add-in.
10.3.2 Setting Up the Custom Menus
To activate the mock features of our add-in shell, we will create a custom menu. We will discuss
the creation of menus and toolbars at length in Chapter 12 . For now, we will keep the details to a
minimum so we can get the overall picture of add-in creation.
Our custom menu should be created automatically when the add-in loads and destroyed when the
add-in unloads. Accordingly, we begin by placing some code in the Open and BeforeClose events
of ThisWorkbook, as shown in Example 10-1 .
Example 10-1. The Workbook's Open and BeforeClose Event Handlers
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
Private Sub Workbook_Open()
End Sub
This event code just calls procedures to create or delete the custom menu. These procedures
should be placed in a new code module, so add a module to the SRXUtils project and name it
basMenus . Next, place the CreateCustomMenuItem procedure shown in Example 10-2 in
basMenus . It is not necessary to completely understand this procedure now, since we will go
over the details in Chapter 12 . For the moment, note that Example 10-2 creates an ActivateSheet
menu item on the Custom menu, and that when we click the item, the routine defined by its
OnAction property—in this case, the ActivateSheet subroutine—is run.
Example 10-2. The CreateCustomMenuItem Procedure
Sub CreateCustomMenuItem()
Dim cbcpop As CommandBarControl
' Check for custom menu. If it exists then exit.
Set cbcpop = Application.CommandBars( _
"Worksheet menu bar"). _
FindControl(Type:=msoControlPopup, _
If Not cbcpop Is Nothing Then Exit Sub
' Control does not exist -- create it.
Set cbcpop = Application.CommandBars( _
"Worksheet menu bar"). _
Controls.Add(Type:=msoControlPopup, _
cbcpop.Caption = "Cu&stom"
Search JabSto ::

Custom Search