Fill a ListBox with values from different columns

[brendalpzm]

Let's say I have the following table

Sheet name: Values

	A​	B​	C​	D​
1	Folio	Model	Year	Status
2	156	Forte	2022	Delivered
3	452526	Rio	2023	In repair
4	785	Stinger	2023	Delivered
5	475	Forte	2020	In progress

An I want to fill an ActiveX List Box but only with the values from columns A,B and D, and also only from year 2023.

How would the VBA code could work? my list box is in a form inside the excel that pops up when I click an activex button.

 

[Herakles]

Let's say I have the following table

Sheet name: Values

	A​	B​	C​	D​
1	Folio	Model	Year	Status
2	156	Forte	2022	Delivered
3	452526	Rio	2023	In repair
4	785	Stinger	2023	Delivered
5	475	Forte	2020	In progress

An I want to fill an ActiveX List Box but only with the values from columns A,B and D, and also only from year 2023.

How would the VBA code could work? my list box is in a form inside the excel that pops up when I click an activex button.

Do you want this solution to work on all of these versions: 365, 2021, 2019, 2016?

How many rows have you got?

 

[brendalpzm]

Do you want this solution to work on all of these versions: 365, 2021, 2019, 2016?

How many rows have you got?

I'm currnetly using 365, but it will be nice just in case if it works in other versions as well.

The number of row can increase or decrease, so it might be dynamic range.

 

[Herakles]

I'm currnetly using 365, but it will be nice just in case if it works in other versions as well.

The number of row can increase or decrease, so it might be dynamic range.

Put this in the Userform Code Module.

Listbox is called lstValues.

Private Sub UserForm_Initialize()
Dim arrData() As Variant
Dim lngIndex As Long
Dim lngRow As Long

    With Me.lstValues
        .ColumnCount = 3
        .ColumnWidths = "60,60,60"
        .Font.Size = 12
    End With
    
    arrData = Worksheets("Values").Range("A1").CurrentRegion.Value

    lngRow = 0
    
    For lngIndex = LBound(arrData, 1) + 1 To UBound(arrData, 1)
        If arrData(lngIndex, 3) = 2023 Then
            With Me.lstValues
                .AddItem
                .List(lngRow, 0) = arrData(lngIndex, 1)
                .List(lngRow, 1) = arrData(lngIndex, 2)
                .List(lngRow, 2) = arrData(lngIndex, 4)
            End With
            lngRow = lngRow + 1
        End If
    Next lngIndex

End Sub

 

[brendalpzm]

Put this in the Userform Code Module.

Listbox is called lstValues.

Private Sub UserForm_Initialize()
Dim arrData() As Variant
Dim lngIndex As Long
Dim lngRow As Long

    With Me.lstValues
        .ColumnCount = 3
        .ColumnWidths = "60,60,60"
        .Font.Size = 12
    End With
   
    arrData = Worksheets("Values").Range("A1").CurrentRegion.Value

    lngRow = 0
   
    For lngIndex = LBound(arrData, 1) + 1 To UBound(arrData, 1)
        If arrData(lngIndex, 3) = 2023 Then
            With Me.lstValues
                .AddItem
                .List(lngRow, 0) = arrData(lngIndex, 1)
                .List(lngRow, 1) = arrData(lngIndex, 2)
                .List(lngRow, 2) = arrData(lngIndex, 4)
            End With
            lngRow = lngRow + 1
        End If
    Next lngIndex

End Sub

IT WORKED THANK YOU SO MUCH

 

[brendalpzm]

Put this in the Userform Code Module.

Listbox is called lstValues.

Private Sub UserForm_Initialize()
Dim arrData() As Variant
Dim lngIndex As Long
Dim lngRow As Long

    With Me.lstValues
        .ColumnCount = 3
        .ColumnWidths = "60,60,60"
        .Font.Size = 12
    End With
   
    arrData = Worksheets("Values").Range("A1").CurrentRegion.Value

    lngRow = 0
   
    For lngIndex = LBound(arrData, 1) + 1 To UBound(arrData, 1)
        If arrData(lngIndex, 3) = 2023 Then
            With Me.lstValues
                .AddItem
                .List(lngRow, 0) = arrData(lngIndex, 1)
                .List(lngRow, 1) = arrData(lngIndex, 2)
                .List(lngRow, 2) = arrData(lngIndex, 4)
            End With
            lngRow = lngRow + 1
        End If
    Next lngIndex

End Sub

If I want to show the headers of those columns what shoul I modify from here?

 

[Herakles]

If I want to show the headers of those columns what shoul I modify from here?

See image.

Set up another listbox called lstHeaders and place it above the lstValues one.

This will just have one row that cannot be selected.

Replace the previous code with this.

Private Sub UserForm_Initialize()
Dim arrData() As Variant
Dim lngIndex As Long
Dim lngRow As Long

    arrData = Worksheets("Values").Range("A1").CurrentRegion.Value

    With Me.lstHeaders
        .ColumnCount = 3
        .ColumnWidths = "60,60,60"
        .Font.Size = 12
        .Font.Bold = True
        .Enabled = False
         .AddItem
        .List(lngRow, 0) = arrData(1, 1)
        .List(lngRow, 1) = arrData(1, 2)
        .List(lngRow, 2) = arrData(1, 4)
    End With

    With Me.lstValues
        .ColumnCount = 3
        .ColumnWidths = "60,60,60"
        .Font.Size = 12
    End With
    
    lngRow = 0
    
    For lngIndex = LBound(arrData, 1) + 1 To UBound(arrData, 1)
        If arrData(lngIndex, 3) = 2023 Then
            With Me.lstValues
                .AddItem
                .List(lngRow, 0) = arrData(lngIndex, 1)
                .List(lngRow, 1) = arrData(lngIndex, 2)
                .List(lngRow, 2) = arrData(lngIndex, 4)
            End With
            lngRow = lngRow + 1
        End If
    Next lngIndex

End Sub