Excel VBA - Trouble Filtering Worksheet Based on ListBox Selections

[sax2play]

Hello All,

I am trying to filter a worksheeet based on the selections a user makes in a listbox; however, when the sheet filters, all of the data is blank. The listbox is populated dynamically with a set of timestamps from the column that will be filtered on based on selection. Looking for any help in why my code is not working.

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    'Set workbook and worksheet variables
    Dim WB As Workbook: Set WB = ActiveWorkbook
    Dim shCompare As Worksheet
    Set shCompare = WB.Worksheets("Fault Comparison")
    
    'Filter MERS Report
    Dim rngFound As Range
    Dim Q As Long, n As Long
    
    With Me.ListBox1
        Dim arr() As Variant
        Dim Q As Long, n As Long
        For Q = 0 To .ListCount - 1
            If .Selected(Q) Then
                ReDim Preserve arr(0 To n)
                arr(n) = .List(Q)
                n = n + 1
            End If
        Next Q
    End With
    
    Set rngFound = WB.Sheets("Fault Comparison").Range("5:5").Find(What:="Test Timestamp", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
    
    shCompare.Select
    shCompare.Cells(5, rngFound.Column).Select
    rngFound.AutoFilter Field:=rngFound.Column, Criteria1:=arr, Operator:=xlFilterValues

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    

End Sub

Thanks in advance for any assistance with this!

 

[DanteAmor]

How is your timestamp on the sheet?
How is your timestamp in the listbox?
Could you put an image to see how the data is?


This way works for me.

Private Sub CommandButton1_Click()
  Dim WB As Workbook
  Dim shCompare As Worksheet
  Dim arr() As Variant
  Dim Q As Long, n As Long
  Dim rngFound As Range
  
  Set WB = ActiveWorkbook
  Set shCompare = WB.Worksheets("Fault Comparison")

  'Filter MERS Report
  With Me.ListBox1
    For Q = 0 To .ListCount - 1
      If .Selected(Q) Then
        ReDim Preserve arr(0 To n)
        arr(n) = .List(Q)
        n = n + 1
      End If
    Next Q
  End With

  shCompare.Select
  Set rngFound = shCompare.Range("5:5").Find("Test Timestamp", , xlValues, xlWhole, , , True)
  If Not rngFound Is Nothing Then
    rngFound.AutoFilter Field:=rngFound.Column, Criteria1:=arr, Operator:=xlFilterValues
  End If
End Sub

 

[sax2play]

This is before I attempt to filter:

This is after attempting to filter:

It does not matter if I select one or multiple - it always filters the sheet to blank.

Thanks!

 

[sax2play]

I'm not sure if this matters or not, but when running through the code, the date selected in the listbox is converted to number format for .List(Q) variable (see picture below, though hard to see). If I change the column being filtered to number format, it still filters the sheet to blank.

Thanks!

 

[DanteAmor]

works for me.

How are you loading the data into the listbox?
Could you share all your code?

And maybe a copy of your file to review.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[sax2play]

Links to files below. Open file 11_2_2022 and run the SV_Compare_New Macro from VBA for the code. The macro will prompt you to select the other file (SV file), which will pull the data into the sheet. That data is then used to populate the listbox. I currently have "Stop" in place for when it gets to the section for filtering.

I only have code so far for filtering from the timestamp listbox, not the other two.

Thanks in advance for all your help and I apologize for the code - I am not a coder at all so it is very chaotic/unorganized.

11_2_2022 file:

11_2_2022_10_34_55 test.xlsm

Shared with Dropbox

www.dropbox.com

SV File:

SV_Report_01-Nov-2022_15-57-01 UTC+0000 - test.xlsx

Shared with Dropbox

www.dropbox.com

 

[sax2play]

works for me.

View attachment 78640

How are you loading the data into the listbox?
Could you share all your code?

And maybe a copy of your file to review.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Dante - were you able to take a look by chance and see why I cannot get my sheet to filter properly?

Thanks for all your help!

 

[DanteAmor]

I did a test and it didn't work because in your userform code you have this line:
Set Filter_Sheets = WB.Worksheets("ConfigFilter_Sheets")


And that sheet does not exist in any of the files.

In the button you have this sheet, it doesn't exist in the book either.
Set shCompare = WB.Worksheets("Fault Comparison")

Either way, I modified your code and the filter works.

I return the two files. you should test the code with these 2 files.

SV_Report_01-Nov-2022_15-57-01 UTC+0000 - test dam.xlsx

Cumulative Fault Report 1,2,3,4,5,6,Report Date/Time blank,,01-Nov-2022 15:57:05 UTC Test Timestamp,Shift,Test Type,Eng Dev,Test Name,Station #,P/F,Fault #,Fault Description 28-Oct-2022 10:26:41,1,S,56,F,32994,TESTS BYPASSED FOR DEVELOPMENT 28-Oct-2022 10:26:41,1,S,56,F,40715,UNKNOWN CONTROLLER ...

docs.google.com

11_2_2022_10_34_55 test dam.xlsm

drive.google.com

 

[sax2play]

I did a test and it didn't work because in your userform code you have this line:
Set Filter_Sheets = WB.Worksheets("ConfigFilter_Sheets")


And that sheet does not exist in any of the files.

In the button you have this sheet, it doesn't exist in the book either.
Set shCompare = WB.Worksheets("Fault Comparison")

Either way, I modified your code and the filter works.
View attachment 78855


I return the two files. you should test the code with these 2 files.

SV_Report_01-Nov-2022_15-57-01 UTC+0000 - test dam.xlsx

Cumulative Fault Report 1,2,3,4,5,6,Report Date/Time blank,,01-Nov-2022 15:57:05 UTC Test Timestamp,Shift,Test Type,Eng Dev,Test Name,Station #,P/F,Fault #,Fault Description 28-Oct-2022 10:26:41,1,S,56,F,32994,TESTS BYPASSED FOR DEVELOPMENT 28-Oct-2022 10:26:41,1,S,56,F,40715,UNKNOWN CONTROLLER ...

docs.google.com

11_2_2022_10_34_55 test dam.xlsm

drive.google.com

Thank you for all of your help, Dante!

I ran through your sheet and compared to mine and found that my filter was not working due to the date getting converted to "custom" cell formatting instead of "General." When I transfer the date column from the original sheet to the new sheet, I originally set the values equal to each other. When the value got populated into the listbox, it put the date as a numerical value, which it would then not find in the sheet as the sheet showed the date as an actual date. Copying and pasting the dates to the new sheet resolved this issue.

Thanks again for all your help!