Excel - add to "Worksheet Menu Bar"

[jON R]

I have found an article that suggests how to add to menu
items to the "Worksheet Menu Bar"

It is for Excel 97 and refers to

CommandBars("Worksheet Menu Bar")

Is this still the accepted method to add/remove a bespoke
item to the main menu? (I am using OXP)

many thanks,

jON

 

[Bob Phillips]

Jon,

Standard approach. Here's an example that adds a new menu before the help
menu

Sub AddMenu()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCustomMenu.Caption = "MyMenu"

With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 1"
.OnAction = "macro1" End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 2"
.OnAction = "macro2" End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 3"
.OnAction = "macro3"
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jON R]

much obliged Bob.

jON

-----Original Message-----
Jon,

Standard approach. Here's an example that adds a new menu before the help
menu

Sub AddMenu()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars ("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCustomMenu = cbMainMenuBar.Controls.Add (Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCustomMenu.Caption = "MyMenu"

With cbcCustomMenu.Controls.Add (Type:=msoControlButton)
.Caption = "item 1"
.OnAction = "macro1" End With
With cbcCustomMenu.Controls.Add (Type:=msoControlButton)
.Caption = "item 2"
.OnAction = "macro2" End With
With cbcCustomMenu.Controls.Add (Type:=msoControlButton)
.Caption = "item 3"
.OnAction = "macro3"
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.