Fill and sort multicol listbox from VBA code?

[Jens Meier]

Hello NG,

on a UserForm, I have got a multicolumn ListBox. 2 questions:

(1) Is it possible to fill a multicolumn listbox out of an array
variable, without the need to specify a RowSource from a worksheet, but
including column heads?

(2) Is there a possibility to let Excel sort the listbox items after a
click on the column headers, taking that column as the search criteria?

Thank you in advance,
Jens

-> peterDavey: I got no reply to my posting, that's why I am asking again.

 

[Robin Hammond]

Jens,

1. I have never got the column heads to work when using an array, but almost
always use arrays anyway, and then add a label above each column of the
listbox.

2. Name the label above column 0 lblCol0, etc.

Sub lblCol0_Click
SortListboxByColumn lst1, 0
End Sub

Sub lblCol1_Click
SortListboxByColumn lst1, 1
End Sub

Sub SortListboxByColumn(L As MSForms.ListBox, lColNumber As Long, _
Optional bAlphabetic As Boolean = True)
'---------------------------------------------------------------------------------------
' Procedure : SortListboxByColumn
' DateTime : 4/15/2004 12:03
' Author : Robin Hammond
' Purpose : sorts a multi column listbox based on values in
lColNumber
' with alphabetic or numeric option
'---------------------------------------------------------------------------------------
'you can remove the line numbers - they are there for my error
handlers

Dim vTemp
Dim lXBound As Long
Dim lYBound As Long
Dim lCounter As Long
Dim lCount2 As Long
Dim strTemp2 As String
Dim lCount3 As Long
Dim bSwitch As Boolean

20 lXBound = L.ListCount - 1
30 lYBound = L.ColumnCount - 1
40 If lXBound < 1 Then Exit Sub
80 ReDim vTemp(0 To lXBound, 0 To lYBound)
90 For lCounter = 0 To lXBound
100 For lCount2 = 0 To lYBound
110 vTemp(lCounter, lCount2) = L.List(lCounter, lCount2)
120 Next lCount2
130 Next lCounter
140 For lCounter = lXBound To 0 Step -1
150 For lCount2 = 1 To lCounter
160 bSwitch = False
170 If bAlphabetic = True Then
180 If UCase(vTemp(lCount2 - 1, lColNumber)) > _
UCase(vTemp(lCounter, lColNumber)) Then bSwitch = True
190 Else
200 If CDbl(vTemp(lCount2 - 1, lColNumber)) > _
CDbl(vTemp(lCounter, lColNumber)) Then bSwitch = True
210 End If
220 If bSwitch = True Then
230 For lCount3 = 0 To lYBound
240 strTemp2 = vTemp(lCount2 - 1, lCount3)
250 vTemp(lCount2 - 1, lCount3) = vTemp(lCounter, lCount3)
260 vTemp(lCounter, lCount3) = strTemp2
270 Next lCount3
280 End If
290 Next lCount2
300 Next lCounter
310 L.Clear
320 L.List = vTemp
End Sub


Robin Hammond
www.enhanceddatasystems.com

 

[Jens Meier]

1. I have never got the column heads to work when using an array, but almost
always use arrays anyway, and then add a label above each column of the
listbox.

2. Name the label above column 0 lblCol0, etc.

Robin,

thanks a lot for your reply.

Regarding 1, at least I know I don't have to go on searching... ;-)
Anyway, the possibility with the labels above the listbox is great and
does what I need, I must admit that I wouldn't have found this solution
myself!

Regarding 2, thanks for the code snippet with the sorting algorithm.
I'll have a look at that and integrate it into my code!

So, your reply saved me a lot of time! Thank you!
Jens

 

[Fran D]

Hi Robin

I had a try to your fantastic code (which I admit I dont fully
understand). Unfortunately I couldnt make it work. I have some probs
putting the "lblColX" at the top of the column heads. Is there any
straight way of doing it?
TIA
FranD

 

[Robin Hammond]

Fran,

Is that for me? I can't find my original post but it sounds like one of my
routines.

I just draw a straight label control above each column of the listbox, then
experiment until I get the placement of the label to match the column
widths.

Name the labels lblCol0, lblCol1 etc, and add an on_click handler for each
label control that calls the sort routine with an index for the column
number.

Does that answer your question?

Robin Hammond
www.enhanceddatasystems.com

 

[Fran D]

Hi Robin
Yes, it was about your code and your reply was really helpful, thanks
again!. I didnt realise that what look like buttons at the top of the
columns are not column headers but labels with a raised effect.
Thanks once again
FranD