Sub-menus for Right Click add ins

  • Thread starter Thread starter JRD
  • Start date Start date
J

JRD

I picked up the following code from this forum and it works just fine to
add items to the right click menu (thanks, guys!):


Public Sub RClick_Additem()

Dim Ctrl As CommandBarControl
Set Ctrl = Application.CommandBars("Cell").Controls.Add
Ctrl.Caption = "Building"
Ctrl.OnAction = "Coverage_Building"

Set Ctrl = Application.CommandBars("Cell").Controls.Add
Ctrl.Caption = "APS"
Ctrl.OnAction = "Coverage_APS"

Set Ctrl = Application.CommandBars("Cell").Controls.Add
Ctrl.Caption = "Contents"
Ctrl.OnAction = "Coverage_Contents"


I have many more items to add to this right click menu and this, of
course, causes the menu to become full. Is there a way to make
sub-menus to the right click menu, like:

Coverages ---> Building
---> APS
---> Contents
Roofing-------->3 Tab
-------->Dimensional
-------->Built-up
etc. and assign macros to each sub-item?

Thanks very much for your help.


Jim
 
Jim

You should be able to incorporate some of the following into your code.


Quoted from XL97: WE1183: "Customizing Menu Bars, Menus, and Menu Items"
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q166755&

The following example adds a new submenu named NewSub on the worksheet Cell
shortcut menu:

Sub ShortcutSub_Create()
CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1) _
.Caption = "NewSub"
' This displays the shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y
' coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub

NOTE: The submenu is empty because no menu items have been added to it yet.

Creating a Menu Item Control on a Submenu Located on a Shortcut Menu Bar:

The following macro adds the command subItem1 to the submenu NewSub that
you created on the Cell shortcut menu, and then runs the Code_subItem1
macro when you click subItem1:

Sub ShortcutSub_AddItem()
Dim newSubItem as Object
Set newSubItem = CommandBars("Cell").Controls("NewSub")
With newSubItem
.Controls.Add(Type:=msoControlButton, before:=1).Caption = _
"subItem1"
' This will run the subItem1_Code macro when subItem1 is clicked.
.Controls("subItem1").OnAction = "Code_subItem1"
End With
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y
' coordinates
CommandBars("Cell").ShowPopup 200, 200
End Sub

Gord Dibben Excel MVP
 
That's working perfectly, after some adaptation. I appreciate all the
help I'm getting here.


Jim
 
Back
Top