Link to home
Start Free TrialLog in
Avatar of randys106
randys106Flag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of randys106

ASKER

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.