Microsoft Office Tutorials and References
In Depth Information
12.9.2 Setting Up the Custom Menus
By using these constants throughout the add-in, if we need to move any columns in the DataSheet
sheet, all we need to do is change the values of these constants. (This is precisely what symbolic
constants are for!)
12.9.2 Setting Up the Custom Menus
The first step in creating the custom menus for our features is to make a slight alteration in the
code for the Open event for ThisWorkbook . Change the code as shown in Example 12-10 .
Example 12-10. The Revised Versions of ThisWorkbook's Open and Close Events
Private Sub Workbook_Open()
CreateCustomMenus
End Sub
The code for creating the custom menu is more complicated than the one from Chapter 10 because
we must now extract the necessary information from the DataSheet worksheet. There are many
ways to do this, but we have elected to split the process into two procedures. The first procedure,
CreateCustomMenus , checks for the existence of the custom menus using the Tag property. If
the menu exists, it is deleted. Then the procedure calls the second procedure,
CreateCustomMenu , which actually does the menu creation. This is done once for the
worksheet menu bar and once for the chart menu bar. The first procedure is shown in Example 12-
.
Example 12-11. The CreateCustomMenus Procedure
Sub CreateCustomMenus()
' Create custom menu on both worksheets and chartsheets
' menu bars if they do not already exist.
' Use the control's tag property to identify it.
Dim cbc As CommandBarControl
Set cbc = Application.CommandBars( _
"Worksheet menu bar").FindControl( _
Type:=msoControlPopup, Tag:="SRXUtilsCustomMenu")
If Not cbc Is Nothing Then cbc.Delete
CreateCustomMenu "Worksheet Menu Bar"
Set cbc = Application.CommandBars( _
"Chart menu bar").FindControl( _
Type:=msoControlPopup, Tag:="SRXUtilsCustomMenu")
If Not cbc Is Nothing Then cbc.Delete
CreateCustomMenu "Chart Menu Bar"
End Sub
The CreateCustomMenu procedure is shown in Example 12-12 . Note that the OnAction
property of every menu item is set to a procedure called RunUtility , as the "onActivation Proc"
column in Figure 12-3 s hows. This procedure will sort out which menu item was selected and call
the appropriate procedure. To pass the information to RunUtility , we set each control's Tag
property to the name of the procedure and its Parameter property to the name of the workbook that
contains the procedure. (The Tag and Parameter properties are "spare" properties designed to
allow the programmer to store important information, which is precisely what we are doing.) In
 
 
 
 
 
Search JabSto ::




Custom Search