Use Contents of Drop Down List

T

tomwesnick

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.
 
I

Incidental

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
 
T

tomwesnick

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
 
C

Chip Pearson

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
Excel 2013: New row added automatically but first 2 columns Drop down lists not available... 0
How to dynamically change the list in drop down list based on thevalue of another cell? 1
Drop down list - link to another worksheet 4
Run or Call a sub from the list of subs in a drop down. 8
Pick from drop-down list gives empty or erroneous result 6
Using Drop Down list to an If Statement to another Drop Down list 3
Filtered drop down list 2

Top