Drop down menus

[Guest]

Can anyone tell me how to get a drop down menu (say called 'Feeds') on the
Standard toolbar between the Data and Window drop downs and from that menu
items so that I can 'attach' them to macros

Many thanks

James

 

[Guest]

James,

Paste something like this in a code module:

'CreateMyMenu will create the menu
'DeleteMyMenu deletes the menu
'Test is a sub executed in the menu item

Sub CreateMyMenu()
Dim cmdBar As CommandBar
Dim cmdBarMenu As CommandBarControl
Dim cmdBarMenuItem As CommandBarControl

Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
Before:=cmdBar.Controls("Window").Index)
cmdBarMenu.Caption = "M&yMenu"
cmdBarMenu.Tag = "MINE"
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "&Macro1"
.OnAction = "Test"
.Tag = "MINE"
End With
End Sub

Sub DeleteMyMenu()
Dim c As CommandBarControl
On Error Resume Next
For Each c In Application.CommandBars.FindControls(Tag:="MINE")
c.Delete
Next c
End Sub

Sub test()
MsgBox "Hello, World"
End Sub

 

[Guest]

Spot on Vergel

Thanks for that

James

James,

Paste something like this in a code module:

'CreateMyMenu will create the menu
'DeleteMyMenu deletes the menu
'Test is a sub executed in the menu item

Sub CreateMyMenu()
Dim cmdBar As CommandBar
Dim cmdBarMenu As CommandBarControl
Dim cmdBarMenuItem As CommandBarControl

Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup,
Before:=cmdBar.Controls("Window").Index)
cmdBarMenu.Caption = "M&yMenu"
cmdBarMenu.Tag = "MINE"
Set cmdBarMenuItem = cmdBarMenu.Controls.Add
With cmdBarMenuItem
.Caption = "&Macro1"
.OnAction = "Test"
.Tag = "MINE"
End With
End Sub

Sub DeleteMyMenu()
Dim c As CommandBarControl
On Error Resume Next
For Each c In Application.CommandBars.FindControls(Tag:="MINE")
c.Delete
Next c
End Sub

Sub test()
MsgBox "Hello, World"
End Sub