Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
Robin Hammond schrieb:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
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 -- Fran D ------------------------------------------------------------------------ Fran D's Profile: http://www.excelforum.com/member.php...o&userid=28365 View this thread: http://www.excelforum.com/showthread...hreadid=389641 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
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" wrote in message ... 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 -- Fran D ------------------------------------------------------------------------ Fran D's Profile: http://www.excelforum.com/member.php...o&userid=28365 View this thread: http://www.excelforum.com/showthread...hreadid=389641 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill and sort multicol listbox from VBA code?
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 :) -- Fran D ------------------------------------------------------------------------ Fran D's Profile: http://www.excelforum.com/member.php...o&userid=28365 View this thread: http://www.excelforum.com/showthread...hreadid=389641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Is there an easier way to fill this listbox | Excel Programming | |||
R1C1 seems to effect listbox fill in VBA | Excel Programming | |||
Fill a ListBox with items using VBA | Excel Programming | |||
For..next.. help to fill listbox | Excel Programming |