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

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
Drop Down with hidden values 1
Sorting of table depending on a drop-down-list entry 4
Define the Range of a drop-down list 5
Run or Call a sub from the list of subs in a drop down. 8
Pasting cell contents into a macro 1
Selecting Items from drop down list 1

Top