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? :think:
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. :doh:
Thanks.
:friends:

[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.:hi: :friends:

[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 (http://www.vbaexpress.com/forum/member.php?u=289) . :friends:

Anybody knows how? :help :help :help

[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. :hi: :hi:

This is the solution that I have used. Hope it can help others here. :thumb
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!!! :beerchug: :friends: