Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Excel Menu Nesting syntax confusion

I am trying to nest menu choices on the Cell menu (right click).  I want it to be like this when a user right clicks in a cell:
Level 1
Level2A
Level 3A
Level 3B
Level2B

That is, when they right click they can then chose the command "Level 1".  Then, they can chose "Level 2A" or "Level 2B".  If they chose "Level 2A" another menu appears, with Level 3 choices.
Here is my code:
    Set ContextMenu = Application.CommandBars("Cell")
    
    CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1, Temporary:=True).Caption = "Level1"
    Set myMenu1 = CommandBars("Cell").Controls("Level1")
    
    With myMenu1
      .Controls.Add(Type:=msoControlPopup, before:=1, Temporary:=True).Caption = "Level2A"
    End With
    With myMenu1
      .Controls.Add(Type:=msoControlPopup, before:=2, Temporary:=True).Caption = "Level2B"
    End With
'The next line is the one that is wrong    
    Set myMenu2 = CommandBars("Cell").Controls("Level2A")
    With myMenu2
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "Level3A"
    End With
    With myMenu2
      .Controls.Add(Type:=msoControlButton, before:=2).Caption = "Level3B"
    End With

Open in new window


The first and second level work fine.  The problem comes when I try to reference  the third level.  

Someone please tell me what I am doing wrong.
0
randys106
Asked:
randys106
1 Solution
 
dlmilleCommented:
Try this:

 
Sub menu()
Dim myMenu1 As CommandBarControl
Dim myMenu2 As CommandBarControl

    Set ContextMenu = Application.CommandBars("Cell")
    
    CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1, Temporary:=True).Caption = "Level1"
    Set myMenu1 = CommandBars("Cell").Controls("Level1")
    
    With myMenu1
      .Controls.Add(Type:=msoControlPopup, before:=1, Temporary:=True).Caption = "Level2A"
    End With
    With myMenu1
      .Controls.Add(Type:=msoControlPopup, before:=2, Temporary:=True).Caption = "Level2B"
    End With

    Set myMenu2 = myMenu1.Controls("Level2A")
    With myMenu2
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "Level3A"
    End With
    With myMenu2
      .Controls.Add(Type:=msoControlButton, before:=2).Caption = "Level3B"
    End With


End Sub

Sub test()

    For Each mycontrol In CommandBars("Cell").Controls
        Debug.Print mycontrol.Caption
    Next mycontrol
End Sub

Open in new window


Dave
0
 
randys106Author Commented:
I had gotten to the right neighborhood but Dave got me to the right house.  The solution is straightforward and elegant.  thanks for the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now