I would like to have a custom menu with a drop down list. It would look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the spreadsheet is
opened. When the user selects a value from the list I would like to get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu from the
toolbox.

Regards,
Leif

Here is an example

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
I would like to have a custom menu with a drop down list. It would look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the spreadsheet
is
opened. When the user selects a value from the list I would like to get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu from the
toolbox.

Regards,
Leif

Bob,

Thanks very much! Is there an event (like a form load or open) that I can
use to initialize the control when the spreadsheet is opened?

Also, how do I reference the control value in VBA?

Thanks.

"Bob Phillips" wrote:

Here is an example

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
I would like to have a custom menu with a drop down list. It would look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the spreadsheet
is
opened. When the user selects a value from the list I would like to get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu from the
toolbox.

Regards,
Leif

Hi Leif,

I think this is what you want

Private Sub Workbook_Open()
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
Bob,

Thanks very much! Is there an event (like a form load or open) that I can
use to initialize the control when the spreadsheet is opened?

Also, how do I reference the control value in VBA?

Thanks.

"Bob Phillips" wrote:

Here is an example

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
I would like to have a custom menu with a drop down list. It would
look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the
spreadsheet
is
opened. When the user selects a value from the list I would like to
get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu from
the
toolbox.

Regards,
Leif

Bob,

Thanks for your reply. As for the second part of my question I was able to
find that the text property
(Application.CommandBars("Test").Controls(1).Text) returns the selected text.
However, each time I start Excel I get another drop down list.

I did use temporary, as you said. From what I've read once the container
application, Excel, closes the control should be gone, but it is not. I'm up
to five controls now. My codes is as follows:

Private Sub Workbook_Open()
With Application.CommandBars("Test")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With
End Sub

Regards,
Leif

"Bob Phillips" wrote:

Hi Leif,

I think this is what you want

Private Sub Workbook_Open()
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
Bob,

Thanks very much! Is there an event (like a form load or open) that I can
use to initialize the control when the spreadsheet is opened?

Also, how do I reference the control value in VBA?

Thanks.

"Bob Phillips" wrote:

Here is an example

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
I would like to have a custom menu with a drop down list. It would
look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the
spreadsheet
is
opened. When the user selects a value from the list I would like to
get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu from
the
toolbox.

Regards,
Leif

Always best to be doubly sure Leif

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Test").Controls("myDD").D elete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next 'just in case
Application.CommandBars("Test").Controls("myDD").D elete
On Error GoTo 0

With Application.CommandBars("Test")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With
End Sub

You can also pick up the value a bit more directly

Sub myDDMacro()
MsgBox CommandBars.ActionControl.Control.Text
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
Bob,

Thanks for your reply. As for the second part of my question I was able
to
find that the text property
(Application.CommandBars("Test").Controls(1).Text) returns the selected
text.
However, each time I start Excel I get another drop down list.

I did use temporary, as you said. From what I've read once the container
application, Excel, closes the control should be gone, but it is not. I'm
up
to five controls now. My codes is as follows:

Private Sub Workbook_Open()
With Application.CommandBars("Test")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With
End Sub

Regards,
Leif

"Bob Phillips" wrote:

Hi Leif,

I think this is what you want

Private Sub Workbook_Open()
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
Bob,

Thanks very much! Is there an event (like a form load or open) that I
can
use to initialize the control when the spreadsheet is opened?

Also, how do I reference the control value in VBA?

Thanks.

"Bob Phillips" wrote:

Here is an example

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "myDD"
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.OnAction = "myDDMacro"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leif" wrote in message
...
I would like to have a custom menu with a drop down list. It
would
look
similar to the zoom % or font drop down list.

I would like to initialize the list programmatically when the
spreadsheet
is
opened. When the user selects a value from the list I would like
to
get
control programatically to perform an action (via VBA)

Is this possible? I've not been able to add a combobox to a menu
from
the
toolbox.

Regards,
Leif