How to add ListIndex into ListBox in VBA Excel

[virakbot]

Dear All Masters,

I want to make my ListBox to be tidy with ListIndex. It means that I want to have straight line column in ListBox. Currently, I don't know how to input ListIndex into below code. Pls kindly help me accordingly. Thanks all Masters.

Sub Pending()
Dim r As Long
Dim msg As String, i As Long, a As Variant




UserForm1.ListBox1.Clear


For i = 13 To 100000
If Range("F" & i) <> "" And Cells(i, 14).Value = "" Then


UserForm1.ListBox1.AddItem Format(Range("E" & i), "dd-mmm") & " " & Range("F" & i) & " " & Range("G" & i) & " " & Range("K" & i)


End If
Next


UserForm1.Show


End Sub

 

[Norie]

What would you use ListIndex for in that code?

ListIndex indicates which item has been selected in the listbox.

 

[virakbot]

Sir !! Sorry, I don’t know exactly about function of ListIndex, and maybe I am wrong to talk about it. Above codes can only provide me a ListBox with untidy data. I want it to be tidy as it is in table, straight column, something like this. thx sir

 

[jkpieterse]

You are probably referring to the ColumnCount property, which sets the number of columns in a listbox:

Sub Pending()
    Dim r As Long
    Dim msg As String, i As Long, a As Variant
    With UserForm1.ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "25;25;25;15"
    For i = 13 To 100
        If Range("F" & i) <> "" And Cells(i, 14).Value = "" Then
            .AddItem Range("E" & i).Value
            .List(.ListCount - 1, 1) = Range("F" & i).Value
            .List(.ListCount - 1, 2) = Range("G" & i).Value
            .List(.ListCount - 1, 3) = Range("K" & i).Value
        End If
    Next
    UserForm1.Show
    End With
End Sub

 

[virakbot]

You are probably referring to the ColumnCount property, which sets the number of columns in a listbox:

Sub Pending()
    Dim r As Long
    Dim msg As String, i As Long, a As Variant
    With UserForm1.ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "25;25;25;15"
    For i = 13 To 100
        If Range("F" & i) <> "" And Cells(i, 14).Value = "" Then
            .AddItem Range("E" & i).Value
            .List(.ListCount - 1, 1) = Range("F" & i).Value
            .List(.ListCount - 1, 2) = Range("G" & i).Value
            .List(.ListCount - 1, 3) = Range("K" & i).Value
        End If
    Next
    UserForm1.Show
    End With
End Sub

Sir, After run it, there is no data in ListBox, May you advise me solution accordingly?

 

[virakbot]

Sir, after run it, there is no data in ListBox, may you advise me solution accordingly? thx sir

 

[jkpieterse]

I changed the high value of the loop counter to 100 rather than the 100000 you had. FOrgot to change it back, apologies!

 

[virakbot]

It works perfectly Sir.. Thanks you very much !!! You are amazing !!!!

 

[Akuini]

Dear All Masters,

I want to make my ListBox to be tidy with ListIndex. It means that I want to have straight line column in ListBox. Currently, I don't know how to input ListIndex into below code. Pls kindly help me accordingly. Thanks all Masters.

Hi, virakbot
Glad that jkpieterse found a solution for you.
But just an idea:
Do you really have 100000 rows of data?
I guess it will take long to loop directly in the range as your code does. So you may want to use an array and do the loop there, it will be faster.