Filtering reports using listboxes in a form

[EliseFreedman]

Hi there

I am attempting to create a "report dashboard" to pull together a series of reports. The ultimate aim is that users can select a series of criteria using multiselect listboxes on a userform. When the items are selected in the listbox, they are dumped in a worksheet which is then used to filter the report

They can filter one report at a time or all the reports at once

I thought my code was working but when I started delving into the results properly, it appears that when the criteria are being selected some of the items in the listboxes are randomly being missed out of the filter.For Example I am filtering by Function, Department Code and Site. When I filter by one function, it will return all sites, when I filter by another function, it misses off a couple of the same sites as the previous function even though when I check there is data matching my criteria. Can anyone suggest where I am going wrong or is there another way of filtering data using multiselect listboxes?

I am using the code below

Sub AutoFilter_Using_Arrays_var_rng()


Dim j As Long
Dim k As Long
Dim l As Long
Dim m As Long

'select the sheet containing the filter values,


Sheets("ReportCriteria").Select
'set the lenght of the array
'I only needed 10 values so 100 covers pretty much that
Dim arCriteria(0 To 2000) As String
Dim arcriteria2(0 To 2000) As String
Dim arcriteria3(0 To 2000) As String
Dim arcriteria4(0 To 2000) As String


'set each cell in the range as array member
'my data was in col "D" so I didn't bother to change that


'dept code
For j = 2 To [d1999].End(xlUp).Row
 arCriteria(j - 2) = Cells(j, "d")
Next j

'site
For k = 2 To [d1999].End(xlUp).Row
 arcriteria2(k - 2) = Cells(k, "e")
Next k

'status
For l = 2 To [d1999].End(xlUp).Row
 arcriteria3(l - 2) = Cells(l, "f")
Next l

'function
For m = 2 To [d1999].End(xlUp).Row
 arcriteria4(m - 2) = Cells(m, "c")
 Next m
 
 
'select the sheet whre I need to apply the filter
'named it "2"
If UserForm1.OptionButton1 = True Then
Sheets("Report_DSETraining").Select
'set the filter for values in column "D" - pure coicidence- column is identified through "field:=4"
With [a1]
.AutoFilter field:=6, Criteria1:=arCriteria, Operator:=xlFilterValues
.AutoFilter field:=5, Criteria1:=arcriteria2, Operator:=xlFilterValues
.AutoFilter field:=13, Criteria1:=arcriteria3, Operator:=xlFilterValues
.AutoFilter field:=4, Criteria1:=arcriteria4, Operator:=xlFilterValues

 End With

End If

If UserForm1.OptionButton2 = True Then
Sheets("Report_AADrivetech").Select
'set the filter for values in column "D" - pure coicidence- column is identified through "field:=4"
With [a1]
.AutoFilter field:=6, Criteria1:=arCriteria, Operator:=xlFilterValues
.AutoFilter field:=5, Criteria1:=arcriteria2, Operator:=xlFilterValues
.AutoFilter field:=16, Criteria1:=arcriteria3, Operator:=xlFilterValues
.AutoFilter field:=4, Criteria1:=arcriteria4, Operator:=xlFilterValues
MsgBox arCriteria
MsgBox arcriteria2
MsgBox arcriteria3
MsgBox arcriteria4

 End With

End If

If UserForm1.OptionButton3 = True Then
Sheets("Report_PassportTraining").Select
'set the filter for values in column "D" - pure coicidence- column is identified through "field:=4"
With [a1]
.AutoFilter field:=6, Criteria1:=arCriteria, Operator:=xlFilterValues
.AutoFilter field:=5, Criteria1:=arcriteria2, Operator:=xlFilterValues
.AutoFilter field:=23, Criteria1:=arcriteria3, Operator:=xlFilterValues
.AutoFilter field:=4, Criteria1:=arcriteria4, Operator:=xlFilterValues
 End With

End If

 

[SkipVought]

Elsie,

Sure would be simpler to see your workbook if you can sanitize to for us.

Just some general comments from browsing your code:

As long as you have lists containing the allowable unique values for Function, Department Code and Site, you have a built-in array. Using Formulas > Defined Names > Create from selection > Create names from vslues in the TOP row, will result in Range Names of Function, Department_Code and Site. Then you can loop thru each.

Then, I’d recommend converting your operating tables to Structured Tables, via Insert > Tables > Table. Tables are named and columns are named by the heading values. Can simplify your VBA greatly.

And finally, I have an aversion to Userforms in Excel. I’d be more apt to use a Dashboard sheet.

I also see that you might have a FORTRAN background with integer variables j, k, l, m. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

When I filter by one function, it will return all sites, when I filter by another function, it misses off a couple of the same sites as the previous function even though when I check there is data matching my criteria.

That description implies that you have dependent data. Not every Function value in your report tables, will display every Site value, it seems.

To verify that behavior, put a filter on the report in question and filter by a single Function. Then see what Sites are visible or not. Repeat for each Function value.

Quite often, what I’d do for dependent lists is perform a query using the first level (ie Function) criteria value(s) to generate a DISTINCT list of the next level (ie Department Code) for the user to select from given the Function selections previously made, and then on to a third selection list for Site, given the selections made for Function and Department Code.

This could all be avoided, by allowing the user direct access to the report tables to select in the headind filters, which is why I avoid using User forms.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]