Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Developing a code that uses a combobox for an autofilter criteria
I am developing a program using Visual Basic on Excel to automate the filter
function. Excel has its autofilter option but I created a userform with a combobox and a command button and the idea is to type the filter criteria (Example: I type pumps) onto the combobox so that on clicking the command button, it automatically filters the specified item. I have a list of items on a worksheet, about 700 items. Unfortunately, I'm unable to link the criteria to the user specified filter item (via the combobox). My code isn't working. I tried first with a textbox and switched to a combobox but it isn't working. How do I link a combobox so that it filters the user-inputted item? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Developing a code that uses a combobox for an autofilter criteria
If i understand you correctply, you are working in VBA in Excel
I assume that your data are in columns A, B, C and D and in rows 4 to ..... (you can change it in MakeFilter sub in ARange and after clearing filters On form i have one combo box and one command button (just to activate filters. General idea is using Excel's own filter system All you had to do is deactivate filters (ClearFilter sub) before you apply new one (this sub) here are subs... Public Sub MakeFilter() Dim flt As String Dim MaxRow Dim ARange As Range Set ARange = Range("A4:A65535") MaxRow = 65535 - WorksheetFunction.CountBlank(ARange) 'This is last used row i assume there is no blank cells in this column ClearFilter 'Filter must be disabled before activating new one... Range("A3:D" & MaxRow).Select 'Must start one row before data Selection.AutoFilter Field:=2, Criteria1:="=*" & ComboBox1.Text & "*", VisibleDropDown:=True Cells(1, 1).Select End Sub Public Sub ClearFilter() On Error Resume Next If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter End If End Sub Private Sub CommandButton1_Click() MakeFilter End Sub On 10.02.2010 14:37, Bebe_dash wrote: I am developing a program using Visual Basic on Excel to automate the filter function. Excel has its autofilter option but I created a userform with a combobox and a command button and the idea is to type the filter criteria (Example: I type pumps) onto the combobox so that on clicking the command button, it automatically filters the specified item. I have a list of items on a worksheet, about 700 items. Unfortunately, I'm unable to link the criteria to the user specified filter item (via the combobox). My code isn't working. I tried first with a textbox and switched to a combobox but it isn't working. How do I link a combobox so that it filters the user-inputted item? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Developing a code that uses a combobox for an autofilter criteria
Also combobox on form should pu combobox with parameters
frmFilter.ComboBox1.RowSource = "B4:B" & 65535 - WorksheetFunction.CountBlank(Range("A4:A65535")) frmFilter.ComboBox1.ControlSource = "B4" With this paramters combobox will be filled with data in columns B from first row (row 4 in example) till last filled row you can combine this with sub from my last reply On 10.02.2010 14:37, Bebe_dash wrote: I am developing a program using Visual Basic on Excel to automate the filter function. Excel has its autofilter option but I created a userform with a combobox and a command button and the idea is to type the filter criteria (Example: I type pumps) onto the combobox so that on clicking the command button, it automatically filters the specified item. I have a list of items on a worksheet, about 700 items. Unfortunately, I'm unable to link the criteria to the user specified filter item (via the combobox). My code isn't working. I tried first with a textbox and switched to a combobox but it isn't working. How do I link a combobox so that it filters the user-inputted item? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter by combobox selection | Excel Programming | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
Make Combobox act like autofilter | Excel Programming | |||
VBA question - autofilter combobox | Excel Programming | |||
Code to allow user to enter criteria for autofilter | Excel Programming |