Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort multicolumn listbox items?
Hello everybody,
on a UserForm, I have got a multicolumn ListBox. 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? Btw, another question: is it possible to fill a multicolumn listbox out of an array variable, without the need to specify a RowSource from a worksheet? Thank you in advance, Jens |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort multicolumn listbox items?
Jens,
the following code loads a multi-column list box with the names and number formats of the data fields of a pivot table. The trick is to load the data into an array variable first the assign that array to the list box List property: E.g. Me.lstDataItem.List() = varListArray. The data could just as easily be read from an Excel range. cheers peterDavey Melbourne Public Sub LoadDataItemList() Dim varListArray() As Variant Dim objPivotTable As PivotTable Dim objPivotField As PivotField Dim intRow As Integer Set objPivotTable = Me.PivotTables(1) ' re-dimension the array to the number of items in the DataFields collection. ReDim varListArray(objPivotTable.DataFields.Count - 1, 1) Me.lstDataItem.Clear intRow = 0 For Each objPivotField In objPivotTable.DataFields varListArray(intRow, 0) = objPivotField.Name varListArray(intRow, 1) = objPivotField.NumberFormat intRow = intRow + 1 Next objPivotField Me.lstDataItem.List() = varListArray Me.lstDataItem.ListIndex = 0 End Sub "Jens Meier" wrote in message ... Hello everybody, on a UserForm, I have got a multicolumn ListBox. 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? Btw, another question: is it possible to fill a multicolumn listbox out of an array variable, without the need to specify a RowSource from a worksheet? Thank you in advance, Jens |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort multicolumn listbox items?
"peterDavey" schrieb
Jens, the following code loads a multi-column list box with the names and number formats of the data fields of a pivot table. The trick is to load the data into an array variable first the assign that array to the list box List property: E.g. Me.lstDataItem.List() = varListArray. Peter, thanks a lot for that code snippet. However, the question is whether it is possible to set column headers with this method. I.e., the contents of the first row of the array sould be interpreted as the contents for the column headers. E.g.: myArray(0,0) = "Header 1" myArray(1,0) = 1 myArray(2,0) = 2 myArray(0,1) = "Header 2" myArray(1,1) = 3 myArray(2,1) = 4 sould turn into the Listbox: Header 1 Header 2 -------------------- 1 3 2 4 Is it possible to achieve something like this? Thanks again! Jens |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populate multicolumn listbox? | Excel Programming | |||
Hyperlink in multicolumn listbox | Excel Programming | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
Sort Listbox Items | Excel Programming | |||
Values in a MultiColumn Listbox | Excel Programming |