Menu problem

[thomasks]

I have a menu that I am adding to excel. However I have one submenu that is showing up in the wrong place. Here is my code:

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
 in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
 Set cbMainMenuBar = _
     Application.CommandBars("Worksheet Menu Bar")
     
'(3)Return the Index number of the Help menu. We can then use _
 this to place a custom menu before.
 iHelpMenu = _
     cbMainMenuBar.Controls("Help").Index
     
 '(4)Add a Control to the "Worksheet Menu Bar" before Help.
 'Set a CommandBarControl variable to it
 Set cbcCustomMenu = _
     cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
                  Before:=iHelpMenu)
                  
     '(5)Give the control a caption
     cbcCustomMenu.Caption = "&CLXX"
     
 '(6)Working with our new Control, add a sub control and _
 give it a Caption and tell it which macro to run (OnAction).
 'Repeat step "6a" for each menu item you want to add.
  With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Assign Area"
                .OnAction = "Sheet2.cmdArea_Click"
 End With
  With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Run Totals"
                .OnAction = "Sheet2.cmdTotal"
 End With
  '(6a)Add another sub control give it a Caption _
  and tell it which macro to run (OnAction)
 Set cbcCustomMenu = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
   ' Give the control a caption
    cbcCustomMenu.Caption = "Branches"
 With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Run Branches"
                .OnAction = "Sheet2.cmdBranches_Click"
 End With
 With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Undo Branches this sheet"
                .OnAction = "Sheet2.Undo_Branches"
 End With
 
 'Add another menu that will lead off to another menu
 'Set a CommandBarControl variable to it
 ' Give the control a caption
 Set cbcCustomMenu = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
    cbcCustomMenu.Caption = "Format ELM Data"
 With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "ALL motorized units"
                .OnAction = "Sheet2.Format_Data_ClickALL"
 End With
 With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "CLXX units only"
                .OnAction = "Sheet2.Format_Data_ClickCLXX"
 End With


End Sub

The problem is with the sub menu "Branches" after I place it in the menu tree, the next one "Format ELM Data" appears under the "Branches" menu instead of under the main menu item called "CLXX" where I want it. Do any of you guys (or gals) see where I went wrong?
Thanks for taking a look.

 

[PHV]

Perhaps this ?
...
Set [!]cMenu1[/!] = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCustomMenu.Caption = "Branches"
With [!]cMenu1[/!].Controls.Add(Type:=msoControlButton)
.Caption = "Run Branches"
.OnAction = "Sheet2.cmdBranches_Click"
End With
With [!]cMenu1[/!].Controls.Add(Type:=msoControlButton)
.Caption = "Undo Branches this sheet"
.OnAction = "Sheet2.Undo_Branches"
End With

'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
' Give the control a caption
Set [!]cMenu1[/!] = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
cbcCustomMenu.Caption = "Format ELM Data"
With [!]cMenu1[/!].Controls.Add(Type:=msoControlButton)
.Caption = "ALL motorized units"
.OnAction = "Sheet2.Format_Data_ClickALL"
End With
With [!]cMenu1[/!].Controls.Add(Type:=msoControlButton)
.Caption = "CLXX units only"
.OnAction = "Sheet2.Format_Data_ClickCLXX"
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thomasks]

Thanks! That did the trick