Nested Sub Menus

S

Scott

In CreateSubMenusTest() below, I'm trying to created a nested sub menu item.
If you run my code, it will create a "Submenu Test" custom menu on the chart
menu. My problem is Group 3. I'm trying to "nest" or make "Group 3 - Sub
Menu 1" and "Group 3 - Sub Menu 2" become nested sub menu's under "Group 3".
No matter what I've tried, I can never get the 2 sub menu groups to move to
a second menu level and become sub menu items of "Group 3".

Can someone help modify my code to achieve this? I included some helper
functions to speed testing the menu.



' CODE ******************************



Sub CreateSubMenusTest()

On Error Resume Next
Application.CommandBars("SubMenu Test").Delete
On Error GoTo 0

Dim HelpMenu As CommandBarControl, NewMenu As CommandBarPopup
Dim Menuitem As CommandBarControl, SubMenuitem As CommandBarButton

' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars("Chart Menu Bar").FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "SubMenu Test"

' 1st main menu item
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Menuitem
.Caption = "Group 1"
.BeginGroup = True
End With

' 1st submenu item
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 1 - Sub Menu Item 1"
.OnAction = "g1Sub1Item1"
End With

' 2nd submenu item
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 1 - Sub Menu Item 2"
.OnAction = "g1Sub1Item2"
End With

' 2nd main menu item
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Menuitem
.Caption = "Group 2"
.BeginGroup = True
End With

' 1st submenu item
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 2 - Sub Menu Item 1"
.OnAction = "g2Sub1Item1"
End With

' 2nd submenu item (Level 2)
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 2 - Sub Menu Item 2"
.OnAction = "g2Sub1Item2"
End With

' 3rd main menu item (Level 1)
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Menuitem
.Caption = "Group 3"
.BeginGroup = True
End With

' *** Nested 3rd menu group ***
' 3rd main menu item 1 (Level 2)
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Menuitem
.Caption = "Group 3 - SubMenu 1"
End With

' 1st submenu item (Level 3)
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 1"
.OnAction = "g3Sub1Item1"
End With

' 2nd submenu item (Level 3)
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 2"
.OnAction = "g3Sub1Item2"
End With

' 3rd main menu item 2 (Level 2)
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Menuitem
.Caption = "Group 3 - SubMenu 2"
End With

' 1st submenu item (Level 3)
Set SubMenuitem = Menuitem.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 2 - Menu Item 1"
.OnAction = "g3Sub2Item1"
End With

' add a menu item that will restore the original menus
Set Menuitem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With Menuitem
.Caption = "Remove Menu"
.OnAction = "DeleteMenu"
.BeginGroup = True
End With

End Sub



Sub DeleteMenu()
Call enabletoolbars
'Application.DisplayFormulaBar = True
CommandBars("Chart Menu Bar").Reset
End Sub

Sub disabletoolbars()
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
End Sub
Sub enabletoolbars()
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
End Sub
 
H

Homey

add this new dim:

Dim Popup As CommandBarPopup, SubPopup As CommandBarPopup

code i change:

' *** Nested 3rd menu group ***
' 3rd main menu item 1 (Level 2)
Set Popup = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With Popup
.Caption = "Group 3 - SubMenu 1"
End With
' 1st submenu item (Level 3)
Set SubMenuitem = Popup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 1"
.OnAction = "g3Sub1Item1"
End With
' 2nd submenu item (Level 3)
Set SubMenuitem = Popup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 2"
.OnAction = "g3Sub1Item2"
End With
' 3rd main menu item 2 (Level 2)
Set SubPopup = Popup.Controls.Add _
(Type:=msoControlPopup)
SubPopup.Caption = "Group 3 - SubMenu 2"
' 1st submenu item (Level 3)
Set SubMenuitem = SubPopup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 2 - Menu Item 1"
.OnAction = "g3Sub2Item1"
End With
' add a menu item that will restore the original menus



| In CreateSubMenusTest() below, I'm trying to created a nested sub menu
item.
| If you run my code, it will create a "Submenu Test" custom menu on the
chart
| menu. My problem is Group 3. I'm trying to "nest" or make "Group 3 - Sub
| Menu 1" and "Group 3 - Sub Menu 2" become nested sub menu's under "Group
3".
| No matter what I've tried, I can never get the 2 sub menu groups to move
to
| a second menu level and become sub menu items of "Group 3".
|
| Can someone help modify my code to achieve this? I included some helper
| functions to speed testing the menu.
|
|
|
| ' CODE ******************************
|
|
|
| Sub CreateSubMenusTest()
|
| On Error Resume Next
| Application.CommandBars("SubMenu Test").Delete
| On Error GoTo 0
|
| Dim HelpMenu As CommandBarControl, NewMenu As CommandBarPopup
| Dim Menuitem As CommandBarControl, SubMenuitem As CommandBarButton
|
| ' Delete the menu if it already exists
| Call DeleteMenu
|
| ' Find the Help Menu
| Set HelpMenu = CommandBars("Chart Menu Bar").FindControl(ID:=30010)
|
| If HelpMenu Is Nothing Then
| ' Add the menu to the end
| Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
| (Type:=msoControlPopup, _
| temporary:=True)
| Else
| ' Add the menu before Help
| Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
| (Type:=msoControlPopup, _
| Before:=HelpMenu.Index, _
| temporary:=True)
| End If
|
| ' Add a caption for the menu
| NewMenu.Caption = "SubMenu Test"
|
| ' 1st main menu item
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlPopup)
| With Menuitem
| .Caption = "Group 1"
| .BeginGroup = True
| End With
|
| ' 1st submenu item
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 1 - Sub Menu Item 1"
| .OnAction = "g1Sub1Item1"
| End With
|
| ' 2nd submenu item
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 1 - Sub Menu Item 2"
| .OnAction = "g1Sub1Item2"
| End With
|
| ' 2nd main menu item
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlPopup)
| With Menuitem
| .Caption = "Group 2"
| .BeginGroup = True
| End With
|
| ' 1st submenu item
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 2 - Sub Menu Item 1"
| .OnAction = "g2Sub1Item1"
| End With
|
| ' 2nd submenu item (Level 2)
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 2 - Sub Menu Item 2"
| .OnAction = "g2Sub1Item2"
| End With
|
| ' 3rd main menu item (Level 1)
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlPopup)
| With Menuitem
| .Caption = "Group 3"
| .BeginGroup = True
| End With
|
| ' *** Nested 3rd menu group ***
| ' 3rd main menu item 1 (Level 2)
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlPopup)
| With Menuitem
| .Caption = "Group 3 - SubMenu 1"
| End With
|
| ' 1st submenu item (Level 3)
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 3 - SubMenu 1 - Menu Item 1"
| .OnAction = "g3Sub1Item1"
| End With
|
| ' 2nd submenu item (Level 3)
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 3 - SubMenu 1 - Menu Item 2"
| .OnAction = "g3Sub1Item2"
| End With
|
| ' 3rd main menu item 2 (Level 2)
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlPopup)
| With Menuitem
| .Caption = "Group 3 - SubMenu 2"
| End With
|
| ' 1st submenu item (Level 3)
| Set SubMenuitem = Menuitem.Controls.Add _
| (Type:=msoControlButton)
| With SubMenuitem
| .Caption = "Group 3 - SubMenu 2 - Menu Item 1"
| .OnAction = "g3Sub2Item1"
| End With
|
| ' add a menu item that will restore the original menus
| Set Menuitem = NewMenu.Controls.Add _
| (Type:=msoControlButton)
| With Menuitem
| .Caption = "Remove Menu"
| .OnAction = "DeleteMenu"
| .BeginGroup = True
| End With
|
| End Sub
|
|
|
| Sub DeleteMenu()
| Call enabletoolbars
| 'Application.DisplayFormulaBar = True
| CommandBars("Chart Menu Bar").Reset
| End Sub
|
| Sub disabletoolbars()
| Application.CommandBars("Standard").Visible = False
| Application.CommandBars("Formatting").Visible = False
| End Sub
| Sub enabletoolbars()
| Application.CommandBars("Standard").Visible = True
| Application.CommandBars("Formatting").Visible = True
| End Sub
|
|
 
S

Scott

That was close, but "Group 3 - SubMenu 1" and "Group 3 - SubMenu 2" and
their child items are now on different levels. Can you take one more look at
your example code?
 
H

Homey

maybe this what you want

' *** Nested 3rd menu group ***
' 3rd main menu item 1 (Level 2)
Set Popup = Menuitem.Controls.Add _
(Type:=msoControlPopup)
With Popup
.Caption = "Group 3 - SubMenu 1"
End With
' 1st submenu item (Level 3)
Set SubMenuitem = Popup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 1"
.OnAction = "g3Sub1Item1"
End With
' 2nd submenu item (Level 3)
Set SubMenuitem = Popup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 1 - Menu Item 2"
.OnAction = "g3Sub1Item2"
End With
' 3rd main menu item 2 (Level 2)
Set SubPopup = Menuitem.Controls.Add _
(Type:=msoControlPopup)
SubPopup.Caption = "Group 3 - SubMenu 2"
' 1st submenu item (Level 3)
Set SubMenuitem = SubPopup.Controls.Add _
(Type:=msoControlButton)
With SubMenuitem
.Caption = "Group 3 - SubMenu 2 - Menu Item 1"
.OnAction = "g3Sub2Item1"
End With
' add a menu item that will restore the original menus

| That was close, but "Group 3 - SubMenu 1" and "Group 3 - SubMenu 2" and
| their child items are now on different levels. Can you take one more look
at
| your example code?
|
|
| "Homey" <none> wrote in message
| | > add this new dim:
| >
| > Dim Popup As CommandBarPopup, SubPopup As CommandBarPopup
| >
| > code i change:
| >
| > ' *** Nested 3rd menu group ***
| > ' 3rd main menu item 1 (Level 2)
| > Set Popup = NewMenu.Controls.Add _
| > (Type:=msoControlPopup)
| > With Popup
| > .Caption = "Group 3 - SubMenu 1"
| > End With
| > ' 1st submenu item (Level 3)
| > Set SubMenuitem = Popup.Controls.Add _
| > (Type:=msoControlButton)
| > With SubMenuitem
| > .Caption = "Group 3 - SubMenu 1 - Menu Item 1"
| > .OnAction = "g3Sub1Item1"
| > End With
| > ' 2nd submenu item (Level 3)
| > Set SubMenuitem = Popup.Controls.Add _
| > (Type:=msoControlButton)
| > With SubMenuitem
| > .Caption = "Group 3 - SubMenu 1 - Menu Item 2"
| > .OnAction = "g3Sub1Item2"
| > End With
| > ' 3rd main menu item 2 (Level 2)
| > Set SubPopup = Popup.Controls.Add _
| > (Type:=msoControlPopup)
| > SubPopup.Caption = "Group 3 - SubMenu 2"
| > ' 1st submenu item (Level 3)
| > Set SubMenuitem = SubPopup.Controls.Add _
| > (Type:=msoControlButton)
| > With SubMenuitem
| > .Caption = "Group 3 - SubMenu 2 - Menu Item 1"
| > .OnAction = "g3Sub2Item1"
| > End With
| > ' add a menu item that will restore the original menus
| >
| >
| >
| > | > | In CreateSubMenusTest() below, I'm trying to created a nested sub menu
| > item.
| > | If you run my code, it will create a "Submenu Test" custom menu on the
| > chart
| > | menu. My problem is Group 3. I'm trying to "nest" or make "Group 3 -
Sub
| > | Menu 1" and "Group 3 - Sub Menu 2" become nested sub menu's under
"Group
| > 3".
| > | No matter what I've tried, I can never get the 2 sub menu groups to
move
| > to
| > | a second menu level and become sub menu items of "Group 3".
| > |
| > | Can someone help modify my code to achieve this? I included some
helper
| > | functions to speed testing the menu.
| > |
| > |
| > |
| > | ' CODE ******************************
| > |
| > |
| > |
| > | Sub CreateSubMenusTest()
| > |
| > | On Error Resume Next
| > | Application.CommandBars("SubMenu Test").Delete
| > | On Error GoTo 0
| > |
| > | Dim HelpMenu As CommandBarControl, NewMenu As CommandBarPopup
| > | Dim Menuitem As CommandBarControl, SubMenuitem As CommandBarButton
| > |
| > | ' Delete the menu if it already exists
| > | Call DeleteMenu
| > |
| > | ' Find the Help Menu
| > | Set HelpMenu = CommandBars("Chart Menu Bar").FindControl(ID:=30010)
| > |
| > | If HelpMenu Is Nothing Then
| > | ' Add the menu to the end
| > | Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
| > | (Type:=msoControlPopup, _
| > | temporary:=True)
| > | Else
| > | ' Add the menu before Help
| > | Set NewMenu = CommandBars("Chart Menu Bar").Controls.Add _
| > | (Type:=msoControlPopup, _
| > | Before:=HelpMenu.Index, _
| > | temporary:=True)
| > | End If
| > |
| > | ' Add a caption for the menu
| > | NewMenu.Caption = "SubMenu Test"
| > |
| > | ' 1st main menu item
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlPopup)
| > | With Menuitem
| > | .Caption = "Group 1"
| > | .BeginGroup = True
| > | End With
| > |
| > | ' 1st submenu item
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 1 - Sub Menu Item 1"
| > | .OnAction = "g1Sub1Item1"
| > | End With
| > |
| > | ' 2nd submenu item
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 1 - Sub Menu Item 2"
| > | .OnAction = "g1Sub1Item2"
| > | End With
| > |
| > | ' 2nd main menu item
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlPopup)
| > | With Menuitem
| > | .Caption = "Group 2"
| > | .BeginGroup = True
| > | End With
| > |
| > | ' 1st submenu item
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 2 - Sub Menu Item 1"
| > | .OnAction = "g2Sub1Item1"
| > | End With
| > |
| > | ' 2nd submenu item (Level 2)
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 2 - Sub Menu Item 2"
| > | .OnAction = "g2Sub1Item2"
| > | End With
| > |
| > | ' 3rd main menu item (Level 1)
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlPopup)
| > | With Menuitem
| > | .Caption = "Group 3"
| > | .BeginGroup = True
| > | End With
| > |
| > | ' *** Nested 3rd menu group ***
| > | ' 3rd main menu item 1 (Level 2)
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlPopup)
| > | With Menuitem
| > | .Caption = "Group 3 - SubMenu 1"
| > | End With
| > |
| > | ' 1st submenu item (Level 3)
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 3 - SubMenu 1 - Menu Item 1"
| > | .OnAction = "g3Sub1Item1"
| > | End With
| > |
| > | ' 2nd submenu item (Level 3)
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 3 - SubMenu 1 - Menu Item 2"
| > | .OnAction = "g3Sub1Item2"
| > | End With
| > |
| > | ' 3rd main menu item 2 (Level 2)
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlPopup)
| > | With Menuitem
| > | .Caption = "Group 3 - SubMenu 2"
| > | End With
| > |
| > | ' 1st submenu item (Level 3)
| > | Set SubMenuitem = Menuitem.Controls.Add _
| > | (Type:=msoControlButton)
| > | With SubMenuitem
| > | .Caption = "Group 3 - SubMenu 2 - Menu Item 1"
| > | .OnAction = "g3Sub2Item1"
| > | End With
| > |
| > | ' add a menu item that will restore the original menus
| > | Set Menuitem = NewMenu.Controls.Add _
| > | (Type:=msoControlButton)
| > | With Menuitem
| > | .Caption = "Remove Menu"
| > | .OnAction = "DeleteMenu"
| > | .BeginGroup = True
| > | End With
| > |
| > | End Sub
| > |
| > |
| > |
| > | Sub DeleteMenu()
| > | Call enabletoolbars
| > | 'Application.DisplayFormulaBar = True
| > | CommandBars("Chart Menu Bar").Reset
| > | End Sub
| > |
| > | Sub disabletoolbars()
| > | Application.CommandBars("Standard").Visible = False
| > | Application.CommandBars("Formatting").Visible = False
| > | End Sub
| > | Sub enabletoolbars()
| > | Application.CommandBars("Standard").Visible = True
| > | Application.CommandBars("Formatting").Visible = True
| > | End Sub
| > |
| > |
| >
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Excel 2007 with Excel 2003 template 1
menu bar & chart menu bar 10
MenuItem Check Mark 1
menu question 1
Excel 2013 menubar incompatibility? 1
NewMenu 5
How to keep custom menu from being killed until all wkbk copys clo 5
Creating Submenus 1

Top