Creating a Submenu

Menu options containing a black triangle to the right of the menu option name display a submenu of additional commands. Suppose that you want to add a submenu to the Tools menu.

1. To add a submenu to the Tools menu, enter the following statement on one line in the Immediate window:

CommandBars("Worksheet menu bar").Controls("Tools").Controls.

Add(Type:=msoControlPopup, Before:=1).Caption = "My Submenu"

When you press Enter, the above instruction places at the top of the Tools menu (Worksheet menu bar) a custom submenu called My Submenu. The above statement will not work if you haven't entered it on one line.

2. To add a custom command to a submenu, enter the following instruction on one line in the Immediate window:

CommandBars("Worksheet menu bar").Controls("Tools").

Controls("My Submenu").Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Option 1"

When you press Enter, the above instruction places the Option 1 command in the My Submenu on the Tools menu. The above statement will not work if you haven't entered it on one line. You can use the same technique to add more options to your submenu.

The following Colors procedure adds the Colors submenu to the built-in Format menu and places in it four options: red, green, blue, and black. Using these options, you can change the color of the text in a selected worksheet cell or cell ranges. The procedures that follow apply the appropriate color formatting.

Sub Colors()

Dim myMenu As Object Dim mySubMenu As Object

Set myMenu = CommandBars("Worksheet menu bar").Controls("Format") With myMenu

.Controls.Add(Type:=msoControlPopup, Before:=2).Caption = "Colors" End With

Set mySubMenu = myMenu.Controls("Colors") With mySubMenu

.Controls.Add(Type:=msoControlButton).Caption = "Red" .Controls.Add(Type:=msoControlButton).Caption = "Green" .Controls.Add(Type:=msoControlButton).Caption = "Blue" .Controls.Add(Type:=msoControlButton).Caption = "Black" .Controls("Red").OnAction = "ColorRed" .Controls("Green").OnAction = "ColorGreen" .Controls("Blue").OnAction = "ColorBlue" .Controls("Black").OnAction = "ColorBlack" End With End Sub

Sub ColorRed()

ActiveCell.Font.Color = RGB(255, 0, 0) End Sub

Sub ColorGreen()

ActiveCell.Font.Color = RGB(0, 255, 0) End Sub

Sub ColorBlue()

ActiveCell.Font.Color = RGB(0, 0, 255) End Sub

Sub ColorBlack()

ActiveCell.Font.Color = RGB(0, 0, 0) End Sub

0 0

Post a comment