Use Contents of Drop Down List
 

I have a drop down list with about 15 items sourced from a column
(outside the printable area) on the same worksheet. I normally select
each value individually and then print the resulting sheet. I was
looking to automate this process via a macro. Can anybody tell me the
syntax needed for a macro that selects the desired value from the drop
down list. Thanks.

Use Contents of Drop Down List
 

hey hey

i fear you may have to give a little more info on what you want to do
as your post leaves a lot up to imagination, you can add code to the
combobox by right clicking and selecting view code.

something like this may give you an idea of what i mean

Private Sub ComboBox1_Change()

'set a single piece of code to work with the combobox value

MsgBox "You chose " & ComboBox1.Value

'do something different with each value

Select Case ComboBox1.Value

Case 1
MsgBox "add the code you want for this one here"
Case 2
MsgBox "or do something else here"
Case 3
MsgBox "maybe just do nothing"

End Select

End Sub

hope it helps

Steve

Use Contents of Drop Down List
 

Steve,

I'm not looking to add items to the combo box, I'm looking to use the
existing items in the combo box in a macro. For example, if my combo
box has three items (sales, direct cost, overhead) and since each item
(sales, direct cost, overhead) produces a different result, given
other formulas are on the sheet that use the combo box value. I would
like the macro to change the combo box value (ie: sales, direct cost,
overhead), recalculate the sheet, and the print the resulting page for
each value (sales, direct cost, overhead). But in my case I have 15
items. Hope that clarifies what I'm looking to do. Thanks

Use Contents of Drop Down List
 

Change the ListIndex property of the ComboBox.

ComboBox1.ListIndex = 5 'whatever

Note that the ListIndex is 0-based, so the first item is ListIndex = 0, the
second item is ListIndex = 1, and so on up through ListIndex = ListCount -
1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

wrote in message
s.com...
Steve,

I'm not looking to add items to the combo box, I'm looking to use the
existing items in the combo box in a macro. For example, if my combo
box has three items (sales, direct cost, overhead) and since each item
(sales, direct cost, overhead) produces a different result, given
other formulas are on the sheet that use the combo box value. I would
like the macro to change the combo box value (ie: sales, direct cost,
overhead), recalculate the sheet, and the print the resulting page for
each value (sales, direct cost, overhead). But in my case I have 15
items. Hope that clarifies what I'm looking to do. Thanks