Populate Userform ListBox from a range and adding a filter

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I have a UserForm with a ListBox which I am populating from a Range using:

Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Rob

Is it only jpgs you want to filter for?
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

emailScreenshot.jpg


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:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Code:
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,935
Messages
5,598,950
Members
414,268
Latest member
bluebandersnatch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top