Developing a code that uses a combobox for an autofilter criteria


B

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?
 
Ad

Advertisements

M

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: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
 
Ad

Advertisements

M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top