Listbox with columns and columnheads

[MJA]

Hello,

I have numbers in column A.
Numbers
1
2
3
4
…

I have text in column C.
Text
A
B
C
D
…

I want to load the numbers and the text into a listbox.
The listbox must have two columns and columnheads.

Private Sub UserForm_Initialize()
Dim Cells As Range
Set Cells = Sheets(1).Cells(1, 1).CurrentRegion
Set Cells1 = Sheets(1).Cells(1, 3).CurrentRegion
ListBox1.ColumnCount = 2
ListBox1.ColumnHeads = True
For Each Item In Cells
ListBox1.Column(1).AddItem Item
Next
For Each Item In Cells1
ListBox1.Column(2).AddItem Item
Next
End Sub

This code doesn’t work!
Why not I ask you!

[kkknie]

I can't say why it doesn't work, but I've tried everything I knew (which wasn't all that much) and couldn't get it. VBA help seems to suggest that you first populate an array and then set the list box columns to the array using either the List or Column property.

ListBox1.List() = MyArray
ListBox2.Column() = MyArray

Documentation says Column transposes and List does not.

I'd love to hear if anyone could find another way to do it (i.e. using the additem method).

K

[Russell Hauf]

Just set the range to include column B, but then set column B's width to zero. Something like this (you can set the range how you like):

Sub FillListBox()
    Set rng = Range(Cells(2, 1), Cells(1, 3).End(xlDown))
    
    With Sheet1.ListBox1
        .ColumnCount = rng.Columns.Count
        .ListFillRange = rng.Address
        .ColumnHeads = True
        .ColumnWidths = ";0 pt;"
    End With

End Sub

Hope this helps,

Russell

Edit: Please note how I started the range in row 2 not row 1. This is for the Column Headers to work correctly.

[ This Message was edited by: Russell Hauf on 2002-05-17 08:25 ]