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).Con trols
(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

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" wrote in message
...
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).Con trols
(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... 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)

"Juan" wrote in message
...
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).Con trols
(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


.

My pleasure Juan.

--

HTH

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

"Juan" wrote in message
...
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)

"Juan" wrote in message
...
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).Con trols
(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


.