New menu bar item

[Juan]

Hi all

I'm trying to get a new item in the Worksheet Menu Bar
with an Add Inn, originally it was going to be one item
and it worked fine...i got it to appear on the Tools Menu
and to react as I wanted... soon my fellow workmates came
up with a similar need so what I tried is to have an item
on the tools menu that is like a combo menu (i.e.
the "Protection" menu inside the "tools" menu, has 3
items) but i have not been able to get a combo item inside
the tools menu... this is my code for the normal extra
control in the tools menu:

Sub createmenu()

Dim NewItem As CommandBarButton
Dim XLMenu As String
Dim XLCB As String
Dim NewItemName As String

XLCB = "Worksheet Menu Bar"
XLMenu = "Tools"

NewItemName = "Tool name here!"

On Error Resume Next
Application.CommandBars(XLCB).Controls(XLMenu).Controls
(NewItemName).Delete

Set NewItem = Application.CommandBars(XLCB).Controls
(XLMenu).Controls.Add
With NewItem
..Caption = NewItemName
..OnAction = "macro name here!"
..FaceId = 0
..BeginGroup = True
End With

End Sub

I would like this to be a dropdown combo from where now i
choose from several tools....

any help is greatly appreciated...

regards
JS

 

[Bob Phillips]

Juan,

Here is an example

Sub createmenu
Dim NewItem As CommandBarControl
Dim XLMenu As CommandBarControl
Dim XLCB As CommandBar
Dim NewItemName As String

Set XLCB = Application.CommandBars("Worksheet Menu Bar")
Set XLMenu = XLCB.Controls("Tools")

NewItemName = "my dd"

On Error Resume Next
XLMenu.Controls(NewItemName).Delete
On Error GoTo 0

Set NewItem = XLMenu.Controls.Add(Type:=msoControlDropdown,
temporary:=True)
With NewItem
.Caption = NewItemName
.OnAction = "macro name here!"
.BeginGroup = True
End With
With XLMenu.Controls(NewItemName)
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.ListIndex = 1
End With
End Sub

--

HTH

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

 

[Juan]

Bob... thanks!! worked great...

JS

-----Original Message-----
Juan,

Here is an example

Sub createmenu
Dim NewItem As CommandBarControl
Dim XLMenu As CommandBarControl
Dim XLCB As CommandBar
Dim NewItemName As String

Set XLCB = Application.CommandBars("Worksheet Menu Bar")
Set XLMenu = XLCB.Controls("Tools")

NewItemName = "my dd"

On Error Resume Next
XLMenu.Controls(NewItemName).Delete
On Error GoTo 0

Set NewItem = XLMenu.Controls.Add (Type:=msoControlDropdown,
temporary:=True)
With NewItem
.Caption = NewItemName
.OnAction = "macro name here!"
.BeginGroup = True
End With
With XLMenu.Controls(NewItemName)
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.ListIndex = 1
End With
End Sub

--

HTH

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




.

 

[Bob Phillips]

My pleasure Juan.

--

HTH

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