Userform Advanced Filter
 

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

--
Message posted from http://www.ExcelForum.com

Userform Advanced Filter
 

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.

--
Regards,
Tom Ogilvy

"DB100 " wrote in message
...
Hi all

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

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

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

Can anyone help??

thanks


---
Message posted from http://www.ExcelForum.com/

Userform Advanced Filter
 

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

--
Message posted from http://www.ExcelForum.com

Userform Advanced Filter
 

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.

--
Regards,
Tom Ogilvy

"DB100 " wrote in message
...
Hi Tom

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

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

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

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

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


---
Message posted from http://www.ExcelForum.com/

Userform Advanced Filter
 

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.



"DB100 <" wrote:

Hi Tom

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

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

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

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

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Userform Advanced Filter
 

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

--
Message posted from http://www.ExcelForum.com

Userform Advanced Filter
 

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

--
Regards,
Tom Ogilvy


"DB100 " wrote in message
...
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 there
is something selected in the combobox. If it is left blank ( to show all
) it errors.

Can you help
Thanks


---
Message posted from http://www.ExcelForum.com/