Populate UserForm ListBox array based on ComboBox Selection

[Ben888]

I have found this code on another question here and have found it works well for searching based on the first column. I'm not sure how to change the code so it searches column "U" yet retains the F to AS column range. Any assistance is greatly appreciated

Private Sub ComboBox3_Change()

Userform1.ListBox1.Clear

Dim shData As Worksheet
Set shData = ThisWorkbook.Sheets("Dispatch")

Dim rng As Range
Set rng = shData.Range("F3:AS" & shData.Range("F" & shData.Rows.Count).End(xlUp).Row)


Dim recordCount As Long
recordCount = Application.CountIf(rng.Columns(1), Me.ComboBox3.Value)

If recordCount = 0 Then Exit Sub

Dim filteredData() As Variant
ReDim filteredData(1 To recordCount, 1 To rng.Columns.Count)

Dim i As Long
Dim j As Long
Dim numRows As Long

numRows = 0
For i = 1 To rng.Rows.Count
If rng.Cells(i, 1).Value = Userform1.ComboBox3.Value Then
numRows = numRows + 1
For j = 1 To rng.Columns.Count
filteredData(numRows, j) = rng.Cells(i, j).Value
Next j
End If
Next i

With Userform1.ListBox1
If numRows > 0 Then
.ColumnCount = rng.Columns.Count
.ColumnWidths = "130;1;1;80;70;60;1;60;1;1;1;30;40;1;100;75;35;35;60;60;60;40;1;100;1;1;1;1;1;1;1;50;1;1;1;1"
.List = filteredData
.TopIndex = 0
End If
End With


End Sub

 

[Artik]

In the range of F:AS, column U is the sixteenth in succession. So you need to correct the references to the first column and cells to 16. Correct these references:

rng.Columns(1)
rng.Cells(i, 1).Value

BTW. if you don't want to show some columns in the ListBox, you don't have to type 1 on these positions, you can type 0.

Artik

 

[Ben888]

Thankyou Artik. Works as suggested!

 

[Ben888]

Do you know if this code would cause excel to freeze?

 

[Artik]

I don't see anything in this code that would cause Excel to hang. Admittedly, you can improve the performance of the code. In the loop For i ... Next do not read data from cells, but from an array that is created before executing the loop.

Private Sub ComboBox3_Change()

    Me.ListBox1.Clear

    Dim shData As Worksheet
    Set shData = ThisWorkbook.Sheets("Dispatch")

    Dim rng As Range
    Set rng = shData.Range("F3:AS" & shData.Range("F" & shData.Rows.Count).End(xlUp).Row)


    Dim recordCount As Long
    Dim vcmb3Val As Variant
    Dim ColsCount As Long
    
    vcmb3Val = Me.ComboBox3.Value
    recordCount = Application.CountIf(rng.Columns(16), vcmb3Val)
    ColsCount = rng.Columns.Count
    
    If recordCount = 0 Then Exit Sub

    Dim filteredData() As Variant
    ReDim filteredData(1 To recordCount, 1 To ColsCount)

    Dim i As Long
    Dim j As Long
    Dim numRows As Long
    Dim v As Variant
    
    v = rng.Value
    
    numRows = 0
    
    For i = 1 To UBound(v)
        If v(i, 16) = vcmb3Val Then
            numRows = numRows + 1
            For j = 1 To rng.Columns.Count
                filteredData(numRows, j) = v(i, j)
            Next j
        End If
    Next i

    With Me.ListBox1
        If numRows > 0 Then
            .ColumnCount = ColsCount
            .ColumnWidths = "130;1;1;80;70;60;1;60;1;1;1;30;40;1;100;75;35;35;60;60;60;40;1;100;1;1;1;1;1;1;1;50;1;1;1;1"
            .List = filteredData
            .TopIndex = 0
        End If
    End With

End Sub

Artik

 

[Ben888]

The above resolved the issue that was occurring with my original code. Thankyou for the above.

 

[Ben888]

The above code is brilliant! Is it possible to have it search a column based on a 'number' search? So 5, 20, 100 etc

 

[Artik]

It seems that the solution should also work on numbers. If it doesn't work, describe in detail what the problem is.

Artik

 

[uk747]

You could also have a formula on the sheet or Advanced Filter and then populate Listbox result based on a dynamic Named Range

Excel dynamic named range: how to create and use

Learn how to create a dynamic named range in Excel and how to use it in your formulas so that a formula expands automatically to include new data in calculations.

www.ablebits.com

Then wouldn't need to Loop through Data
Me.ListBox1.Rowsource = "myNamedRange"

 

[Ben888]

Hi Artik, no fault is coming up. The ComboBox has 5, 20, 1000 and Misc Vol. When one of the numbers are chosen nothing appears in the ListBox though when 'Misc Vol.' selection is chosen it will populate the ListBox. I have made sure the RowSource on the ComboBox (5, 20, 1000, Misc Vol,) were a direct copy from the range/column that the code is looking in (the RowSource on the ComboBox is referenced to a separate worksheet than the worksheet/range that populates the ListBox).
If I change Dim vcmb3Val As String (instead of Variant), then it works with the numbers but then nothing comes up with 'Misc Vol.' selection. Apologies I may be explaining everything incorrectly terminology/reference wise.