×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Filtering reports using listboxes in a form

Filtering reports using listboxes in a form

Filtering reports using listboxes in a form

(OP)
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

CODE

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 




RE: Filtering reports using listboxes in a form

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. winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Filtering reports using listboxes in a form

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close