Dave, I have assigned a cell for the value of the index but I cannot get my
head round to using it in the macro. The dropdown is indeed from the forms
toolbar which has five tabs in the Format Control properties popup. This has
a tab which allows you to assign an input range for the dropdown.

The second piece of code looks exactly what I want, although I would want to
run it from a macro in another workbook. How would I call it from another
workbook?
When I select the combo or List boxes from the Control toolbar i am unable
to assign an Input range. It is this value I want to select as the filter
criteria.

Also when this is selected, will it run in several macros and possibly
twice in the same macro?

Thanks for your help on this.

Regards,

Davie


"Dave Peterson" wrote:

The name dropdown sounds like the dropdown came from the Forms Toolbar.

That means that the value of the dropdown is an index into the list for that
dropdown. (Try assigning a linked cell to that dropdown and you'll see a
number--not the string that shows up in the dropdown.)

So....

dim myStr as string
With worksheets("Sheet1")
with .dropdowns("drop down 7") '<--watch the spaces!
if .listindex = 0 then
'nothing chosen, what should happen?
'beep and a msgbox and exit sub???
myStr = ""
else
mystr = .list(.listindex)
end if
end with
end with

then use myStr in your autofilter statement.
.AutoFilter Field:=10, Criteria1:=myStr

If I guessed wrong and you used a combobox from the Control Toolbox toolbar,
then you could use the .value of that combobox.

..AutoFilter Field:=10, Criteria1:=worksheets("Sheet1").dropdown7.value



davethewelder wrote:

Hi, I have no experience of combo boxes but I woould like one to filter the
value on a worksheet to be used in a macro. I am trying to make the user
select the value from a list in a combo box called "dropdown7_change" which
is passed to a macro for filtering a field in a spreadsheet. I have the
combo box set up on sheet1 and I require it to filter into the selection
below.

With Selection
.AutoFilter Field:=10, Criteria1:="dropdown7_Change"
.AutoFilter Field:=13, Criteria1:="Y"
.AutoFilter Field:=11, Criteria1:="=Current"
.AutoFilter Field:=12, Criteria1:="=Open for Editing"
End With

Hope you can help.

Davie

--

Dave Peterson