Developing a code that uses a combobox for an autofilter criteria

[Bebe_dash]

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?

 

[Monarch]

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" & 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

 

[Monarch]

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