Populate ListBox from Excel Column

[sheeeng]

How do i populate a listbox from data that contain in column C in worksheets "Sheet1"? I want all cells that contain data be populated in the list box.
How do I know the end of the column C where it is empty?
I need to sort the list box in alphabetical order immediatelly after the list box is populated.

Sub PopulateList()
Dim i, j As Integer
Dim strval As String
j = 2
While Worksheets("Sheet1").Range("C" & j).Formula <> ""
i = i + 1
j = j = 1
Wend
For i = 2 To j
strval = Worksheets("Sheet1").Cells(i, 3).Value
UserForm1.ListBox1.AddItem (strval)
Next i
End Sub

.....

While Worksheets("Sheet1").Range("C" & j).Formula <> ""

The above line also generate error. What went wrong?

What can improve my code?

Please correct me.


Please help.
Thanks.

[mvidas]

Hi Sheeeng,

When filling a listbox, I prefer to add the items to an array then set the .List property to be the array.

If your column C may have blank cells between the first and last used cell in the range, I would recommend using something like:

Dim i As Long, vArray() As Variant, ArrCount As Long
 ArrCount = 0
 ReDim vArray(ArrCount)
 With Sheets("Sheet1")
  For i = 2 To .Range("C65536").End(xlUp).Row
   If .Cells(i, 3) <> "" Then
    ReDim Preserve vArray(ArrCount)
    vArray(ArrCount) = .Cells(i, 3)
    ArrCount = ArrCount + 1
   End If
  Next i
 End With
 UserForm1.ListBox1.List = vArray[/vba]But if you know that there will be no blanks between the first and last used cell in column C, you could simply use:[vba] Dim vArray As Variant
 With Sheets("Sheet1")
  vArray = .Range("C2", .Range("C65536").End(xlUp)).Value
 End With
 UserForm1.ListBox1.List = vArray

Matt

ps. I prefer to use Do/Loop instead of While/Wend, like:

Do While Worksheets("Sheet1").Range("C" & j).Formula <> ""
     j = j = 1
    Loop

I didn't see anything wrong with the syntax you used, though. The only thing I could think of is that you don't have a Sheet1?

[sheeeng]

How do I sort all the data in list box after import everything into the list box?

[mvidas]

Rather than sort it after it is imported, you could either sort the range on the sheet ahead before loading it, or you could sort the array (my preference).

Paste the following into a blank standard module:

Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef lpDest As Any, ByRef lpSource As Any, ByVal iLen As Long)
Public Sub SortStringArray(ByRef sArray() As String)
 Dim iLB As Long, iUB As Long, i As Long, j As Long, vTemp  As String
 iLB = LBound(sArray)
 iUB = UBound(sArray)
 SortStringArrayStep2 sArray, 4, iLB, iUB
 InsertionSortString sArray, iLB, iUB
End Sub

Private Sub SortStringArrayStep2(ByRef sArray() As String, ByVal iSplit As Long, ByVal iLB As Long, ByVal iUB As Long)
 Dim i As Long, j As Long, vTemp As String
 If (iUB - iLB) > iSplit Then
  i = (iUB + iLB) / 2
  If sArray(iLB) > sArray(i) Then SwitchStr sArray(iLB), sArray(i)
  If sArray(iLB) > sArray(iUB) Then SwitchStr sArray(iLB), sArray(iUB)
  If sArray(i) > sArray(iUB) Then SwitchStr sArray(i), sArray(iUB)
  j = iUB - 1
  SwitchStr sArray(i), sArray(j)
  i = iLB
  CopyMemory ByVal VarPtr(vTemp), ByVal VarPtr(sArray(j)), 4 ' vTemp = sArray(j)
  Do
   Do
    i = i + 1
   Loop While sArray(i) < vTemp
   Do
    j = j - 1
   Loop While sArray(j) > vTemp
   If j < i Then Exit Do
   SwitchStr sArray(i), sArray(j)
  Loop
  SwitchStr sArray(i), sArray(iUB - 1)
  SortStringArrayStep2 sArray, iSplit, iLB, j
  SortStringArrayStep2 sArray, iSplit, i + 1, iUB
 End If
 i = 0
 CopyMemory ByVal VarPtr(vTemp), ByVal VarPtr(i), 4
End Sub

Private Sub InsertionSortString(ByRef sArray() As String, ByVal iLB As Long, ByVal iUB As Long)
 Dim i As Long, j As Long, vTemp As String
 For i = iLB + 1 To iUB
  CopyMemory ByVal VarPtr(vTemp), ByVal VarPtr(sArray(i)), 4 ' vTemp = sArray(i)
  j = i
  Do While j > iLB
   If sArray(j - 1) <= vTemp Then Exit Do
   CopyMemory ByVal VarPtr(sArray(j)), ByVal VarPtr(sArray(j - 1)), 4 ' sArray(j) = sArray(j - 1)
   j = j - 1
  Loop
  CopyMemory ByVal VarPtr(sArray(j)), ByVal VarPtr(vTemp), 4
 Next i
 i = 0
 CopyMemory ByVal VarPtr(vTemp), ByVal VarPtr(i), 4
End Sub

Private Sub SwitchStr(ByRef s1 As String, ByRef s2 As String)
 Dim i As Long
 i = StrPtr(s1)
 If i = 0 Then CopyMemory ByVal VarPtr(i), ByVal VarPtr(s1), 4
 CopyMemory ByVal VarPtr(s1), ByVal VarPtr(s2), 4
 CopyMemory ByVal VarPtr(s2), i, 4
End Sub[/vba]Then call it as follows:[vba] Dim i As Long, vArray() As String, ArrCount As Long
 ArrCount = 0
 ReDim vArray(ArrCount)
 With Sheets("Sheet1")
  For i = 2 To .Range("C65536").End(xlUp).Row
   If .Cells(i, 3) <> "" Then
    ReDim Preserve vArray(ArrCount)
    vArray(ArrCount) = .Cells(i, 3)
    ArrCount = ArrCount + 1
   End If
  Next i
 End With
 SortStringArray vArray
 UserForm1.ListBox1.List = vArray

Matt

[sheeeng]

Thanks. That is a good comment.

[mvidas]

Glad to help! You'll be hard-pressed to find a faster sort method too

[sheeeng]

Yes. I would want to know how to sort the data in the ListBox after they are in the ListBox. Thanks a lot, mvidas .

Anybody knows how?

[MWE]

given the nature of the need, i.e., to sort items in a list box, I would expect that the number of items to be sorted is modest. Thus a simple bubble sort of the array would be much simplier and probably no slower.

Sub SimpleSort(X)
'       Function:   sorts virtually any data type from smallest to largest
'       Limitations:    assumes entire array from LBound(X) to UBound(X) is to be sorted
'       Passed Values:
'           X     [in, any]  array of values
Dim I As Long
    Dim J As Long
    Dim Temp
For I = LBound(X) To UBound(X) - 1
        For J = I + 1 To UBound(X)
            If X(I) > X(J) Then
                Temp = X(I)
                X(I) = X(J)
                X(J) = Temp
            End If
        Next J
    Next I
End Sub

[mvidas]

Using the string sort method above, you could add this to your code:

Function SortListBox(ByRef vTemp As Variant)
 Dim vArr(), vSArr() As String, vItm As Variant, i As Long
 vArr = vTemp.List
 i = 0
 For Each vItm In vArr
  ReDim Preserve vSArr(i)
  vSArr(i) = vItm
  i = i + 1
 Next vItm
 SortStringArray vSArr
 vTemp.List = vSArr
End Function

Then when you want to sort the listbox, just call

SortListBox UserForm1.ListBox1

Matt

[sheeeng]

Thanks. It works. Very good post.

[sheeeng]

So, after this sorting codes, the data in the listbox is all in array? How do I read the string that is multiple selected? Thanks.

[sheeeng]

Hello.

This is the solution that I have used. Hope it can help others here.
But it just follow the cells' data. It still cannot sort the list box data alphabetically.

Sub PopulateList(ByVal LineCount As Integer)
Dim i As Integer
Dim strval As String
For i = 2 To LineCount + 1
strval = Worksheets("Sheet1").Cells(i, 3).Value
UserForm1.ListBox1.AddItem (strval)
Next i
End Sub

Thanks to all VBA Express Forum members that help out all beginner like me to the world of VBA!!!