help with submenu

**secretk** · 12-30-2006, 10:21 PM

hi there,

let me first thank you all for all the info and stuff on this site/forum that helped me alot with my excel knowledge.

in this tread i need help with creating more then 1 submenu.
most of the code i found on http://www.ozgrid.com/VBA/excel-add-in-create.htm
the problem is how do i create another menu that will lead off to another menu?
in other words how do i create more then 1 ControlPopup?

**MSP77079** · 12-30-2006, 10:37 PM

Repeat the code for:

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)

This will add another pop-up (drop down) menu.

Keep repeating exactly the same code over and over and you will get exactly the same menu items over and over. Then, go back and change the parts you want to change (like captions, icons, and "on action" macros).

Hope this answered the question.

**secretk** · 12-30-2006, 11:07 PM

indeed i thought it would but the question is where to put the code.
i tried a few options but the other popupmenu goes in the previous popupmenu.
i dont get how to put 2 or more popupmenus below each other.

**MSP77079** · 12-30-2006, 11:19 PM

Well, no wonder I thought the answer was so easy! I was answering the wrong question!!!

Sorry, I have never tried to put multiple layer down-downs. Tell ya what I suspect the answer is ... it can be done but only with great difficulty. You basically have to figure out where in space the command bar is and then create another, floating command bar in the exact spot you want it in.

Hope for your sake I'm wrong. I'll be watching to see what comes up here.

**jtp** · 12-30-2006, 11:50 PM

Give this a shot.

Sub BuildMenu()
Dim cBar As CommandBar, cBarControl As CommandBarControl, cSub As CommandBarControl
Dim cSubPop As CommandBarControl

DeleteToolMenu
Set cBar = Application.CommandBars("Worksheet Menu Bar")
Set cBarControl = cBar.Controls.Add(msoControlPopup, 1, , , True)
With cBarControl
    .Caption = "My Menu"
    .Tag = "MyMenu"
    .Visible = True
End With

Set cSubPop = cBarControl.Controls.Add(msoControlPopup, 1, , , True)
With cSubPop
    .Caption = "Pop Off Menu"
    .Tag = "SubMenu"
    .Visible = True
End With

Set cSub = cSubPop.Controls.Add(Type:=msoControlButton)
With cSub
    .Caption = "Button"
    .OnAction = "YourMacro"
    .FaceId = 23
End With

End Sub

Sub DeleteToolMenu()
Dim cBarControl As CommandBarControl

Set cBarControl = Application.CommandBars("Worksheet Menu Bar").FindControl(msoControlPopup, , "MyMenu")

If Not cBarControl Is Nothing Then cBarControl.Delete

End Sub

HTH

**secretk** · 12-31-2006, 05:36 AM

yes that works
thxs jtp
i was wondering wot this piece of code means in plain english coz im still learning.

Dim cBar As CommandBar, cBarControl As CommandBarControl, cSub As CommandBarControl

below code indeed adds another popupmenu item below the other.

Set cSubPop = cBarControl.Controls.Add(msoControlPopup, 1, , , True)
With cSubPop
    .Caption = "Pop Off Menu"
    .Tag = "SubMenu"
    .Visible = True
End With

Set cSub = cSubPop.Controls.Add(Type:=msoControlButton)
With cSub
    .Caption = "Button"
    .OnAction = "YourMacro"
    .FaceId = 23
End With