Menus in xlmenubar

[Steve davis]

Sub Macro1()
MenuBars(xlWorksheet).Menus.Add Caption:="&Name1", before:="&Help"
With MenuBars(xlWorksheet).Menus("&Extras").MenuItems
.Add Caption:="Name1", OnAction:="Macro2"
.AddMenu Caption:="menu1"
End With
End Sub


how do i add a new caption to menu1 and also the onaction
could anyone please help

 

[Bob Phillips]

Use commandbatrs

Dim idx As Long

On Error Resume Next
Application.CommandBars(1).Controls("&Name1").Delete
On Error GoTo 0

idx = Application.CommandBars.FindControl(ID:=30010).Index
With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup,
before:=idx)

.Caption = "&Name1"

With .Controls.Add(Type:=msoControlButton)

.Caption = "menu1"
.OnAction = "Test"
End With
End With

 

[Steve davis]

hi when this sheet opens up the first thing it has to do is wipe out file to
help to limit the amout of damage users of the sheet can do to it all this
works fine
i then remake a complete new set of menus all this works fine but when i put
a submenu in my menu i cant add to it
like in excel click view then toolbars
but cant add to the toolbars menu

 

[Steve davis]

hi thanks 4 the help had a play a first with no success but got it ight
later this is the new one


Dim idx As Long

idx = Application.CommandBars.FindControl(ID:=30010).Index
With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup,
before:=idx)

.Caption = "&Menu1"

With .Controls.Add(Type:=msoControlButton)
.Caption = "name1"
.OnAction = "Test"

End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "name2"
.OnAction = "Test"

End With

With .Controls.Add(Type:=msoControlPopup)
.Caption = "menu2"

With .Controls.Add(Type:=msoControlButton)
.Caption = "name3"
.OnAction = "Test"
End With
End With
End With
End Sub


with the old xlmenubar option i could add more than one caption between my
end with command.
but when i put name1 and name2 in the same end with only name2 was showing
so did it this way thanks 4 your help its put me on the right track now

 

[Bob Phillips]

You have got the right way to nest the menus. I would just suggest better
indentation to make it more readable

Dim idx As Long

idx = Application.CommandBars.FindControl(ID:=30010).Index
With Application.CommandBars(1).Controls.Add( _
Type:=msoControlPopup, _
before:=idx)

.Caption = "&Menu1"

With .Controls.Add(Type:=msoControlButton)

.Caption = "name1"
.OnAction = "Test"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "name2"
.OnAction = "Test"
End With

With .Controls.Add(Type:=msoControlPopup)

.Caption = "menu2"

With .Controls.Add(Type:=msoControlButton)

.Caption = "name3"
.OnAction = "Test"
End With
End With
End With

 

[Steve davis]

hi thanks again for help will us text ie

' this bit has this

' that bit has that

and also try to put all my T's & R's in missing from last message