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.
randys106Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.