Example The Create CustomMenus 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 shows. 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 the RunUtility procedure, we can use the ActionControl property to return the control that caused the RunUtility procedure to execute. Then it is a simple matter to read the Tag and Parameter properties of that control.

Was this article helpful?

0 0

Post a comment