Microsoft Office Tutorials and References
In Depth Information
12.8 Example: Adding an Item to an Existing Menu
In this macro, we use the FindControl method to locate the list box control, via its tag, on the
toolbar. Once we have located the list box, we can get the currently selected item (which we
simply display for this example). Note that if two or more controls fit the search criteria,
FindControl returns the first control that it finds. Also, if no control fits the criteria, FindControl
returns Nothing , so we can check this as we have done in our program.
12.8 Example: Adding an Item to an Existing Menu
Of course, rather than creating a custom toolbar or adding a custom menu to Excel's menu system,
you may prefer to add a button to an existing toolbar or a menu item to an existing menu. In that
case, you simply need to retrieve a reference to the CommandBar object to which you wish to add
the item and call the Controls collection's Add method to add an item to it. In addition, you can
retrieve the Index property of the item before which you'd like to position your new menu item or
toolbar button. Example 12-9 , which contains the source code for a Workbook_Open event that
adds an "About SRXUtils" menu item immediately before the "About Microsoft Excel" item,
shows how this can be done. Note that the procedure is able to determine the precise location of
the About Microsoft Excel menu item by retrieving a reference to its CommandBarControl object
and its Index property.
Example 12-9. Adding a Menu Item to an Existing Menu
Private Sub Workbook_Open()
Dim lngPos As Long
Dim objHelpMenu As CommandBar
Dim objHelpMenuItem As CommandBarControl
Dim objExcelAbout As CommandBarControl
'Get reference to Help menu
Set objHelpMenu = Application.CommandBars("Help")
' Determine position of "About Microsoft Excel"
Set objExcelAbout = objHelpMenu.Controls("About Microsoft Excel")
If Not objExcelAbout Is Nothing Then
lngPos = objExcelAbout.Index
lngPos = objHelpMenu.Controls.Count
End If
' Add "About SRXUtils" menu item
Set objHelpMenuItem = objHelpMenu.Controls.Add(msoControlButton, _
1, , lngPos, True)
objHelpMenuItem.Caption = "About &SRXUtils"
objHelpMenuItem.BeginGroup = True
objHelpMenuItem.OnAction = "ShowAboutMacros"
End Sub
12.9 Augmenting the SRXUtils Application
Armed with our knowledge of Office CommandBars, we can augment our add-in shell, first
discussed in Chapter 10 .
Search JabSto ::

Custom Search