Autofilter from a multiple selection listbox - Excel VBA

[dbogusl]

I currently have code that populates a listbox with all of the unique data from a particular column. I want to be able to autofilter my worksheet according to the multiple selections I make from the listbox (via VBA).

For example, let's say I have a 1,000 bank transactions on a worksheet and one column is the month of each transaction. My listbox will give me the 12 months (assuming thats how many there were). I just want to be able to select from the listbox which multiple months to display at one time (eg. March & April). Thanks.

 

[DreamerZ]

Have you tried to record a macro to do what you want? On a simple column, this is what was recorded for me:

    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="=a", Operator:=xlOr, Criteria2:="=e"

I know I use autofilters and replace the Criteria with variables as below:

    sheet_Coaching.Range("A1", Cells(find_Last.Row - 1, 5)).AutoFilter Field:=1, Criteria1:=frm_AddCoaching.lbox_Names.Value

I don't use more than 1 Criteria typically and I think you can only use AutoFilter with 2 Criteria max.

I'd start with the basics from the recorded autofilter macro. With multi-select, you'll have to use the Selected property since Value is Null. You could use a Select Case to convert the Selected # to the month string and then set the Criteria for the Filter from that. If you have more than 2 months, you could also use the Hidden property of the Row.EntireRow to hide what isn't selected.

Hope that helps.


DreamerZ
simplesolutions04@sbcglobal.net
[ignore]

http://www.geocities.com/simplesolutions04@sbcglobal.net

[/ignore]

 

[PHV]

If you need more than 2 criterias, you may consider the AdvancedFilter method.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dbogusl]

DreamerZ, My listbox allows for multiple selections. For this reason, the "myform.listbox.value" property will result in an error. The .value property will only work on single selections.

 

[DreamerZ]

My code example was for single select list boxes, as I mentioned. I did also mention the Selected property use for multi-select list boxes: "With multi-select, you'll have to use the Selected property since Value is Null."

Also, I don't know much about the Advanced Filter. I've never used it...PHV would be of more assistance I'm sure.




DreamerZ
simplesolutions04@sbcglobal.net
[ignore]

http://www.geocities.com/simplesolutions04@sbcglobal.net

[/ignore]

 

[VBAjedi]

I agree with PHV - the Advanced filter might be a better tool for this. It's going to be a bit tricky to set up, however. Start by carefully reading the help files on the Advanced Filter. You're going to need to determine the maximum number of simultaneous selections you will allow, and have an equal number of criteria rows. As you will learn when you read the help files, the Advanced Filter needs a seperate criteria row for every seperate set of conditions you want to return (in your example, "rows where the month = March" is one set of criteria, and "rows where the month = April" is a second set of criteria.)

Now you write code to clear the criteria range (except the header row), then loop through the selected items in your listbox, writing each one to the next available criteria row (in the "Month" column). Keep track of how many criteria rows you have used, because you don't want to include blank criteria rows when you run the filter. Finally, you trigger the Advanced Filter (use the Macro Recorder to get the start of the code for that) on your data range, specifying the criteria header and the used criteria rows as your criteria range.

It'll take some work, but it should be feasible!


VBAjedi