Userform Advanced Filter

D

DB100

Hi all

I have a userform which uses a combo boxes to insert data onto a th
next available line on a spreadsheet.

I have been using a manual advanced filter on the spreadsheet to filte
out the differing combinations of information required.

I would like to add a "search" button on the userform, that will, whe
pressed filter out the existing data on the spreadsheet to match th
differing values entered into the combo boxes. I am having trouble a
this may be up to 8 combo boxes.

Can anyone help??

thank
 
T

Tom Ogilvy

You would just write the appropriate information to the criteria range and
then issue the advanced filter command.

The code for writing the information wouldn't be materially different than
code to write the data from the form to the worksheet.

With worksheets("Filterpage")
.Range("B9").Value = Combobox1.Value
End With

If you need code for the advanced filter, turn on the macro recorder while
you apply it manually. It is only one command with several arguments.
 
D

DB100

Hi Tom

Thanks for your help. I am fairly new to this and am struggling to ge
this to work. I think I am nearly there....

I have added the "search" button to the form and so far it opens up th
required spreadsheet, applies the filter. but I am failing to get it t
recognise the value of the combobox as the search criteria.

I am using the below.... ( where Combocountry is the name of the comb
box )

Workbooks.OpenText FileName:="J:date file.xls",
Range("A2").Select
Selection.AutoFilter

With Worksheets("sheet1")
Selection.AutoFilter Field:=1, Criteria1:="Combocountry.Value"
End Wit
 
T

Tom Ogilvy

With Worksheets("sheet1")
Selection.AutoFilter Field:=1, Criteria1:=Combocountry.Value
End With

No double quotes on Combocounty.value

by the way, autofilter and advanced filter are two different things.
 
D

Dave Peterson

Not anything to do with your question, but you may want to specify the full path
in this line:

Workbooks.OpenText FileName:="J:date file.xls"

Workbooks.OpenText FileName:="J:\mypath\date file.xls"
or
Workbooks.OpenText FileName:="J:\date file.xls"

just in case the current directory on the J: drive isn't what you expect.
 
D

DB100

Hi Tom

Just to say thanks a grat deal...this has really helped.

Just one last thing if I may, the code works perfectly as long as ther
is something selected in the combobox. If it is left blank ( to show al
) it errors.

Can you help
Thank
 
T

Tom Ogilvy

With Worksheets("sheet1")
if ComboboxCountry.Value <> "" Then
Selection.AutoFilter Field:=1, Criteria1:=Combocountry.Value
Else
if .AutofilterMode then
.ShowAll
End if
End With
 
Top