Pass listbox values to autofilter

  • Thread starter Thread starter Ixtreme
  • Start date Start date
I

Ixtreme

I have a form with some controls on it. One of them is a multi select
listbox from which the user can select 1, 2 or more values. How can I
pass the values to my autofilter in VBA?
I understand that this is not possible using comboboxes?
 
Hi there,

I have some sample code which might help you get where you want to go. I
don't know exactly what part of the code you need and also I don't know
whether you want to pass multiple selections from the ListBox to one
individual column filter or multiple selections to a number of individual
column filters.

Therefore I am including sample code for setting the criteria of the filters
for both conditions plus sample code for extracting the selected data from
the MultiSelect ListBox. I'm making the assumption that you will then
probably be able to put code together to achieve your goal. However, if not
then I will need some more info on exactly what you want to do.

'Example 1:
'Setting criteria on multiple column filters
Dim numFilt1 As Single
Dim numFilt2 As Single
Dim strFilt1 As String
Dim strFilt2 As String

numFilt1 = 6 'Numeric value
numFilt2 = 10 'Numeric value

strFilt1 = Format(numFilt1, "0") 'Convert to string
strFilt2 = Format(numFilt2, "0") 'Convert to string

Selection.AutoFilter Field:=1, Criteria1:=strFilt1
Selection.AutoFilter Field:=2, Criteria1:=strFilt2

'Example 2:
'Setting custom multiple criteria on one filter with 'Or' condition

Dim numCrit1 As Single
Dim numCrit2 As Single
Dim strCrit1 As String
Dim strCrit2 As String

numCrit1 = 1 'Numeric value
numCrit2 = 5 'Numeric value

'Convert to string with = sign
strCrit1 = "=" & Format(numCrit1, "0")
strCrit2 = "=" & Format(numCrit2, "0")

Selection.AutoFilter Field:=1, Criteria1:=strCrit1, Operator:=xlOr, _
Criteria2:=strCrit2


'Example 3:
'Extracting the selected data from a MultiSelect listbox
'Note: List items start at zero hence 0 to ListCount-1

'You will need your form details in lieu of the sheet details
With Sheets("Sheet1").ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
'this is where you make the criteria = the selections
MsgBox .List(i)
End If
Next i
End With

Regards,

OssieMac
 
One more thing. I had to put a button in to run the code to read the selected
values in MultiSelect TextBox. It does not run from a Private Sub
ListBox1_Click(). This is because it cannot know when you have finished
selecting.

Regards,

OssieMac
 
Thanks for your examples. In my case, I have a form FrmFilter on which
I have comboboxes and 1 listbox called ListBox1. The listbox is
automatically populated with a specific range on a sheet called
"PARMS". So far so good. The user should be able to select 1, 2 or n
selections in this listbox and pass the values to a autofilter. So
like you wrote, what I need is something like:

With Me.ListBox1

For i = 0 To .ListCount - 1
If .Selected(i) = True Then
strCrit1 = "=" & ListBox1.List(i, 0)

End If
Next i
End With

But how can I make this code work to loop through all possible values
(which can vary, depending of number of items on sheet PARMS) and make
sure that each strCritn has it's own unique value.

For example I have a defined range ITEMS that consitst of "A", "B" and
"C".
The user selects in the listbox1 on the userform option "A" and option
"C". Those 2 items need to go to strCrit1 and strCrit2 and then I need
something like this: Selection.AutoFilter Field:=1,
Criteria1:=strCrit1, Operator:=xlOr, Criteria2:=strCrit2

But the next time the user selects let say 4 items. Then the
autofilter should be something like Selection.AutoFilter Field:=1,
Criteria1:=strCrit1, Operator:=xlOr, Criteria2:=strCrit2,
Operator:=xlOr, Criteria3:=strCrit8, Operator:=xlOr,
Criteria4:=strCrit10

Thank you for your help.

Mark
 
What you appear to be describing now is so complex that it needs a flowchart
diagram to properly explain your requirements to someone else. Your initial
question of how to pass the selections from the TextBox to Autofilter was
relatively simple to answer.

I think that you are effectively attempting to duplicate what Autofilter
does. It would be better to simply provide sufficient instructions to the end
users so that they can make their own selections in AutoFilter.

Maybe someone else would like to take it up but I'll bow out of it now
because I think it would be never ending to try to solve your problem on this
forum and I don't wish to spend that much time on it.

Regards,

OssieMac
 
Back
Top