ExcelBanter - Expanding Menu items in VBA

Here is some code that I use for Sub Menus... If it is not clear how to make
this work let me know and I can e-mail you something that will work for you...

Private Function CBAddSubMenu(cbrMenu As CommandBarControl, _
strCaption As String) As Boolean
'pass in the command bar you want to add to and the caption

Dim ctlCBarControl As CommandBarControl

With cbrMenu
Set ctlCBarControl = .Controls.Add(msoControlPopup)
With ctlCBarControl
.Caption = strCaption
.Tag = .Caption
End With
End With
End Function

Private Function CBAddSubMenuControl(cbrMenu As CommandBarControl, _
strMainControl As String, _
strCaption As String, _
strOnAction As String) As Boolean
'Pass in the Command Bar, the drop down, the button caption
'and the action
Dim ctlCBarControl As CommandBarControl

With cbrMenu
Set ctlCBarControl =
..Controls(strMainControl).Controls.Add(msoControl Button)
With ctlCBarControl
.Caption = strCaption
.OnAction = strOnAction
.Tag = .Caption
End With
End With
End Function
--
HTH...

Jim Thomlinson

"ExcelMonkey" wrote:

I have a simple menu set that loads a menu item ("Menu Item") and then
creates two items below it (Sub Item 1 and Sub Item 2). See Code Below

Menu Item
Sub Item 1
Sub Item 2

I now want to create sub items for each of the two items
Menu Item
Sub Item 1
Sub Item 1.1
Sub Item 1.2
Sub Item 2
Sub Item 2.1
Sub Item 2.2

How do I expand the code below to incorporate the new tier of sub menu items.

Thanks EM

NewMenu.Caption = "&Menu Item"

'Set First Tier of Menue Items
'First Menu Item
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Sub Item 1"
.FaceId = 590
'Call macro when chosen
.OnAction = "Macro 1"
End With

'Second Menu Item
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Sub Item 2"
.FaceId = 591
'Call macro when chosen
.OnAction = "Macro 2"
End With