How do I insert a menu

[Robin M]

Hi
I want to program a routine when the workbook is opening.
This routine must insert a menu named "Rubicon" with
submenus names "Init" , "Nameinsert" , "etc...", if its
not there already. And the submenus must be attach to
macros.

Thanks to anyone upfront.

Robin

[Raymond Kelly]

Robin,

Try the following code in the module section.

Public Sub Add_Menu()
Dim RubiconMenuBar
Dim RubiconMenuOption As CommandBarControl
Dim iHelpIndex As Integer
Set RubiconMenuBar = CommandBars("Worksheet Menu Bar")
'If Excel crashed while last opened so that Before_Close()
'event didn't happen
'the Rubicon menubar may still exist. So delete it just in case
On Error Resume Next
RubiconMenuBar.Controls("Rubicon").Delete
On Error GoTo 0
'put Rubicon before the Help Menu
iHelpIndex = RubiconMenuBar.Controls("Help").Index
Set RubiconMenuOption =
RubiconMenuBar.Controls.Add(Type:=msoControlPopup, _
before:=iHelpIndex)

With RubiconMenuOption
.Caption = "Rubicon"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Init"
.OnAction = "InitSub"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Name Insert"
.OnAction = "NameInsertSub"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Etc..."
.OnAction = "EtcSub"
End With
With .Controls.Add(Type:=msoControlPopup)
.Caption = "SubMenu"
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "If Needed 1"
.OnAction = "IfNeeded1Sub"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "If Needed 2"
.OnAction = "IfNeeded2Sub"
End With

End With
End With
End Sub

Public Sub Remove_Menu()
Dim RubiconMenuBar As CommandBar
On Error Resume Next 'Incase it has already been deleted
Set RubiconMenuBar = CommandBars("Worksheet Menu Bar")
RubiconMenuBar.Controls("Rubicon").Delete
End Sub

You would also want to remove the menu when the worksheet is not in use, so
you could add the following code to the 'This Workbook" section.

Private Sub Workbook_Activate()
Call Add_Menu
End Sub

Private Sub Workbook_DeActivate()
Call Remove_Menu
End Sub

HTH
Raymond

"Robin M" <ro...@rpartner.no> wrote in message
news:05e101c30d8b$573c9830$a301...@phx.gbl...

[Robin Motroen]

Hi Raymond Kelly,

Thank you for your code, it help a lot.
I have Excel in a different language and had to use the index# to
identify Help menu.

Since the Help menu usually is the last menu item, I used
this code insted

iHelpIndex = RubiconMenuBar.Controls.Count

Robin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!