Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |