combo box to be used in Macro

[davethewelder]

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]

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]

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]

If you use the dropdown from the Forms toolbar, you have to use the first
macro. Did you have trouble with that code?

If you decide to remove the dropdown from the Forms toolbar and replace it with
a combobox from the Control Toolbox toolbar, then you can click on the Design
Mode icon (also on that control toolbox toolbar) and then rightclick on the
combobox.

Then choose Properties.
You can type in the address of the range you want in the "ListFillRange"
property.

I don't understand what you mean by the last question.

Personally, I like to add a button (or commandbutton) near the dropdown (or
combobox) and use that to start the macro I want to run.

I think it makes it easier to correct mistakes if I chose the wrong option.

 

[davethewelder]

Dave, I have now tried your code and it works great. I did not try it
prevously as I was thinking to far ahead about the process I am creating.
What I meant about running twice in the one macro has now became clearer in
my head.
The macro's I am running are for opening files and filtering using the value
from the combi box.
It is now clear to me that if I include it in all the macros they will
reference the value in the combo box. I have not figured out the syntax to
link the macro from the file that has opened to the combo box.
I tried "With Worksheets("Report Creation.Sheet1") but recieved the
"Subscript out of Range" error. Do I have to include the full path?

Once again, thanks for your advice.
Davie

 

[Dave Peterson]

With workbooks("Report creation.xls").worksheets("sheet1")
or maybe
with Thisworkbook.worksheets("sheet1")
(assumes that report creation is the workbook with the code.)

Dave, I have now tried your code and it works great. I did not try it
prevously as I was thinking to far ahead about the process I am creating.
What I meant about running twice in the one macro has now became clearer in
my head.
The macro's I am running are for opening files and filtering using the value
from the combi box.
It is now clear to me that if I include it in all the macros they will
reference the value in the combo box. I have not figured out the syntax to
link the macro from the file that has opened to the combo box.
I tried "With Worksheets("Report Creation.Sheet1") but recieved the
"Subscript out of Range" error. Do I have to include the full path?

Once again, thanks for your advice.
Davie

 

[Dave Peterson]

Ps. That workbook has to be open, too. And you don't use the drive/path in the
Workbooks() collection--just the name (and extension).

Dave, I have now tried your code and it works great. I did not try it
prevously as I was thinking to far ahead about the process I am creating.
What I meant about running twice in the one macro has now became clearer in
my head.
The macro's I am running are for opening files and filtering using the value
from the combi box.
It is now clear to me that if I include it in all the macros they will
reference the value in the combo box. I have not figured out the syntax to
link the macro from the file that has opened to the combo box.
I tried "With Worksheets("Report Creation.Sheet1") but recieved the
"Subscript out of Range" error. Do I have to include the full path?

Once again, thanks for your advice.
Davie