Populate Userform ListBox from a range and adding a filter

[RobbieC]

Hi there, I have a UserForm with a ListBox which I am populating from a Range using:

Private Sub UserForm_Initialize()
     ListBox1.RowSource = Range(Sheets("FilesInProject").Range("B2"), Sheets("FilesInProject").Range("B65536").End(xlUp)).Address(, , , True)
End sub

Which grabs everything from Sheet "FilesInProject" in column B and throws it into the ListBox in the Userform. Brilliant!

However, in column A on the same sheet I have the type of file, ie PDF, JPG, GIF etc...

What I would like to do is to be able to reload the ListBox data, but with a filter, ie. a button on the userform: 'JPG' which will load in the List from column B, but only if column A has JPG in it....

I also have, in column C, the date when the file was created 11/04/2019 (in that format)... Ultimately, I would like to create a second 'date filter' where it would search for JPG in col A and within the date range in column C... I can get a calendar to select the date ranges ('from' and 'to' in 2 seperate TextBoxes), but that's as far as I have got

If anyone can help point me in the right direction, I'd be very grateful

Thanks

Rob

 

[Norie]

Rob

Is it only jpgs you want to filter for?

 

[RobbieC]

Hi Norie, how's tricks?

No, that was just an example - I have about 8 buttons I want to use as filters. If I can just get it to work with 1, ie 'JPG', then I should be able to replicate the code on seperate buttons for the others,

ie. GIF, PDF, INV, AGS, DPC etc etc - these are all the 3 letter codes in column A

I was thinking of having a row of buttons across the top of the userform: 'ALL' 'JPG' 'GIF' 'PDF' etc which could all run the same code with seperate 'filters' to re-populate the ListBox...

I hope that kinda makes sense

 

[Norie]

How about a dropdown, cboExtensions, for the file extensions and a single button for the filtering?

Or you could even do the filtering without the button using the change event of the file extension dropdown.

Whichever you choose the first thing I would suggest doing is changing the way you populate the listbox

Instead of using RowSource, which 'ties' the listbox to the range it's been populated from you could use List.

While we're at it why not include the dates in a (hidden) column in the listbox?

Option Explicit

Dim arrFileList As Variant

Private Sub cboExtensions_Change()
Dim arrFilteredList As Variant
Dim strExt As String
Dim idx As Long
Dim cnt As Long

    If cboExtensions.ListIndex <> -1 Then
        strExt = UCase(cboExtensions.Value)
    End If
    
    ReDim arrFilteredList(1 To UBound(arrFileList, 2), 1 To UBound(arrFileList, 1))
    
    For idx = LBound(arrFileList, 1) To UBound(arrFileList, 1)
        If UCase(arrFileList(idx, 1)) Like "*." & strExt Then
            cnt = cnt + 1
            arrFilteredList(1, cnt) = arrFileList(idx, 1)
            arrFilteredList(2, cnt) = arrFileList(idx, 2)
        End If
            
    Next idx
    
    If cnt > 0 Then
        ReDim arrfilteredelist(1 To UBound(arrFilteredList, 1), 1 To cnt)
        ListBox1.Column = arrFilteredList
    End If
    
End Sub

Private Sub UserForm_Initialize()
    
    With Sheets("FilesInProject")
        arrFileList = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Resize(, 2).Value
    End With

    With ListBox1
        .ColumnCount = 2
        .ColumnWidths = .Width - 5 & "pt;0pt"
        .List = arrFileList
    End With
    
    cboExtensions.List = Array("GIF", "PDF", "INV2", "AGS", "DPC", "JPG")
    
End Sub

 

[RobbieC]

Hi Norie, thanks for that - looks really cool. This is my Userform (ListBox1 is on the left populated with ALL):

when a user selects from ListBox1 on the left, they can select it for ListBox2, which then goes on to create the attachments in the email.

I would rather stay away from drop down 'select' menus as this is for a touchscreen application and they have proved to be quite fiddly to operate. This is why I have gone for the bigger 'buttons' at the top for filtering.

The code I have on the 'ALL' button is:

Private Sub filterALL_Click()
    Call clearFilters 'resets the colours
    Me.filterALL.BackColor = onBGColour
    Me.filterALL.ForeColor = onTxtColour
    ListBox1.RowSource = Range(Sheets("FilesInProject").Range("B2"), Sheets("FilesInProject").Range("B65536").End(xlUp)).Address(, , , True)
End Sub

Is it possible to adapt your code to work off buttons like this, rather than dropdowns? As long as the contents from column B2 end up in the right hand ListBox2, I think the code will still work ok, although I haven't checked how it works... (I think it takes the 'filename' and re-associates itself with the actual file to email)

 

[Norie]

Rob

I'm sure something can be worked out but the first thing you need to do is change the way you are populating ListBox1.

Like I said if you use RowSource it ties it back to the range it's being populated from and that makes things difficult when it comes to filtering.

As for how the filtering is being done in the code I posted, all that's happening is that the filename is checked against the selected extension and if there's a match the filename, and date, are added to a list.

 

[RobbieC]

Great stuff Norie. I have added the dropdown select and added your code - worked first time! :D

I can hide the dropdownbox and I can actually just control from the top buttons using:

Private Sub filterPDF_Click()
    Call clearFilters
    Me.filterPDF.BackColor = onBGColour
    Me.filterPDF.ForeColor = onTxtColour
    Me.cboExtensions.Value = "PDF"
End Sub

How hard would it be to add the filters for the date range? It's looking awesome at the moment!

Thanks mate

 

[RobbieC]

Hi Norie, I was just playing with the code and trying to work out what's happening...

Is it basing the filtering on the last 3 letters of the filename? If so, I'll have to think of another method. The reason I want to filter it by column A is because that column is where the filetype is referenced.

There can be many PDF's in the list, but some of them are filed under INV or DPC - not the file extension...

This is why I was wanting to filter the results because it was looking confusing...

Is it possible to bring column A into play within ListBox1?

Sorry to be a pain mate

 

[Norie]

Rob

Looking back I misread your original post and assumed you wanted to filter by file extension/type, not the value in column A.

Should be straightforward to bring in column A and use that for filtering rather than the actual filename.

I'll try and post something back later.

I'll also try to post something a bit more generic for the filtering so you can use the buttons, it's seems a bit daft to be using a hidden control.

 

[RobbieC]

Hi Norie, hope you're well.

Did you manage to have a look at this for me? Would be great to get it up and running.

Cheers