Sort multicolumn listbox items?

[Jens Meier]

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

 

[peterDavey]

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]

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