How populate multi-column Listbox control?

[Robert Crandal]

Ok, I'm back with more questions about the Listbox control. The
help documention is not very helpful, so I gotta keep asking here,
haha.

Anyways, my listbox control is set to have 3 columns, but I just can't
figure out how to put data into any column except column 1. How the
heck can I put data in columns 2 or 3???

BTW, I tried the following code which I found through Google, but it
doesn't work:

ListBox1.AddItem "here is some text"
ListBox1.List(1, 1) = "more data"
ListBox1.List(1, 2) = "even more data"

Please help. thank u

 

[Dave Peterson]

When I do this kind of thing, I'm usually looping through something.

So...

Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.Clear
.RowSource = ""
.ColumnCount = 3

For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

Listcount is how many items there are (1 then 2, then 3, ..., then 5 in this
case), but the first item in the list is item 0, so we subtract 1 from the
current listcount.

 

[Robert Crandal]

Dave,

Thanks, that worked great!

BTW, do you know if I can add gridlines or something to the listbox
so I can see the dimensions of each cell in the listbox?? I almost might
want column 3 to be longer than the others.

Thanks again!

 

[Chip Pearson]

You can use ColumnCount to set the number of columns, ColumnWidths to
set the columns widths and pass an array to List for a list box. E.g.,


Private Sub UserForm_Initialize()
Dim NumRows As Long
Dim NumCols As Long
Dim Arr() As String
NumRows = 2
NumCols = 3
ReDim Arr(1 To NumRows, 1 To NumCols)
Arr(1, 1) = "r1 c1"
Arr(1, 2) = "r1 c2"
Arr(1, 3) = "r1 c3"
Arr(2, 1) = "r2 c1"
Arr(2, 2) = "r2 c2"
Arr(2, 3) = "r2,c3"
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "100;30;30"
.List = Arr
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

I don't think Excel's listbox allows that kind of formatting.

And take a look at Chip's suggestion. He builds the array before and just loads
it in one fell swoop.

(And take note of his .columnwidths line, too.)