Auto filter selection from combo box

[cmunnelly]

Could anyone point me in the correct direction to select the criteria for an auto filter via a combo box.

 

[mep1]

Have your combo box select the specific column you want to filter and then depending on the filter criteria the combo box should execute the following code:

Selection.AutoFilter Field:=1, Criteria1:="YOUR CRITERIA HERE"[\code]

Hope this helps...

MEP

 

[Stretchwickster]

This example sets the autofilter drop-down in B1 to the value contained in the combobox cb_DeptNo:

  Range("B1").AutoFilter Field:=2, Criteria1:=cb_DeptNo.Text

You could perhaps put this code in the Change event of the Combobox.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

Keep getting an object required error?

 

[Stretchwickster]

What code are you using?
What is the range in which your autofilter is placed?
What is the name of your combobox?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

Have renamed combobox1 to listbox

Private Sub listbox_Change()
Range("B1&quot.AutoFilter Field:=2, Criteria1:=names.Text

End Sub

 

[Stretchwickster]

Let me get this straight, the code you specified will do the following:

When the user makes a selection from listbox, you look in a combobox(?) called names and use the selected item as criteria for your autofilter located in cell B1.

Can you give an example of what you want to do?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

No sorry about crossed wires. What i want to do is select an item from my combo box a simply use the selected items to filter the data.

 

[Stretchwickster]

I'm assuming the combobox you refer to is one that you created on a form or just loose on the sheet itself and that it is called "names".

In the Change event of the "names" combobox you need to put this code (assuming that the autofilter you want to select from is located in B1 and is the first autofiltered column):

  If Len(names.Text) > 0 Then
    Range("B1").AutoFilter Field:=1, Criteria1:=names.Text
  End If

The Field:=1 argument corresponds to the autofilter in B1 being the first autofiltered column. If Field:=2 then the autofilter in B1 corresponds to the second autofiltered column in the sheet (i.e. there would have to be one in A1).

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

Getting there i think. Still getting an error message before it selects the correct data. The message says:
Autofilter method of range class failed.

Appreciate your help on this one

 

[Stretchwickster]

Try changing the field parameter to 1 then try it to 2. As I said this needs to correspond to the autofilter column i.e. if column B is the first autofiltered column then field should be made equal to 1. If column B is the second autofiltered column then Field should be made equal to 2.

Is your autofilter in column B?
Is it the first column with an autofilter?
What other columns have autofilters?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

Parameter is correct. It is the first filtered column. As i said it actually works but before it does work it produces this error message. Once i end the error messsage it then produces the correct data.

Yes first autofilter is in column B. Column C also has an autofilter

 

[Stretchwickster]

The only way I can reproduce your error is to set the Field to an invalid column i.e. one that doesn't have an autofilter. What data does column B have in it?

I've changed the code a little bit so try this:

If Len(names.Text) > 0 Then
    Range("B1").Select
    Selection.AutoFilter
    Selection.Range("B1").AutoFilter Field:=1, Criteria1:=names.Text
  End If

You could perhaps check the FilterMode property to see if the AutoFilter is on.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[cmunnelly]

The type of data is simple text. Im using excel 2002 if it helps. I take it you mean the autofilter mode should be on?
in cell A1 and A2 there are my column headings with the autofilters on. The dat is simply in column A first names and in column B are surnames.

 

[Stretchwickster]

I can't really see what the problem is then! If you would like, you could send me the file and I'll have a look. stretchwickster@yahoo.com

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

I think the problem lies with how you populate your combobox. You've created a kind of circular reference (using the ListFillRange property) so what happens is:
- you select an item from the combobox
- this triggers the change event of the combobox
- from here, the autofilter for that column is initiated and the autofilter shows the item you selected from the combobox
- this triggers the change event of the combobox again because you have set the source data to point at this autofiltered area.

In order to solve this I would suggest a couple of methods:
1) populate the combobox in code (either in the Workbook Open event (click on ThisWorkbook in the VB Editor) or in a Button's click event):

  ComboBox1.Clear
  ComboBox1.AddItem "Colin"
  ComboBox1.AddItem "Lisa"
  ComboBox1.AddItem "Lyn"

2) Alternatively, create a hidden list of names elsewhere either on the same sheet or another hidden sheet. Set the ListFillRange property of the combobox to point to that area and then you shouldn't get the same problem.

I'll send you my working version so that you can see what I've done.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)