Solved: Add third column to listbox

[RonNCmale]

How would you add a third column to List Box:
I tried changing column count to 3, but am lost from that point.



Private Sub UserForm_Initialize()
Dim rDates As Range

ListBox1.ColumnCount = 2
ListBox2.ColumnCount = 2

With Sheet1
Set rDates = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
rDates.Interior.ColorIndex = xlNone
For Each cl In rDates
Select Case cl.Value
Case Is = Date
With Me.ListBox1
.AddItem cl.Offset(0, -1).Value
.List(.ListCount - 1, 1) = Format(cl.Value, "dd mmm yyyy")
End With
Case Is < Date
With Me.ListBox2
.AddItem cl.Offset(0, -1).Value
.List(.ListCount - 1, 1) = Format(cl.Value, "dd mmm yyyy")
End With
End Select
Next cl
End Sub

[GTO]

Greetings Ron,

The code you show only specifies two columns, and it is not apparent (leastwise not to the thick-headed blond guy) what we want to go into column three?

Mark

[snb]

Columncount doesn't specify the number of columns a listbox/combobox contains, but indicates how many columns will be shown.

If you use:


sub M_snb()
combobox1.list=range("A1:G10").value
end sub


the combobox will contain 7 columns.
You can check that using:

sub M_snb()
combobox1.list=range("A1:G10").value
msgbox ubound(combobox1.list,2)
end sub


NB. the first column is column (0)
You'd better avoid 'additem' to populate a combobox/listbox; but use 'List' instead

[RonNCmale]

I have four columns but want only "Staff Name", "Court Date", Shift to fill up the Userform using three columns.

See attached:

[GTO]

ACK! Thank you snb, I totally spaced that. :omg2:

Mark

@RonNCmale:

I'm signing out, but just as a simple example, this seems to work:
Private Sub UserForm_Initialize()
Dim rDates As Range

ListBox1.ColumnCount = 3
ListBox2.ColumnCount = 3

With Sheet1
Set rDates = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
rDates.Interior.ColorIndex = xlNone
For Each cl In rDates
Select Case cl.Value
Case Is = Date
With Me.ListBox1
.AddItem cl.Offset(0, -1).Value
.List(.ListCount - 1, 1) = Format(cl.Value, "dd mmm yyyy")
.List(.ListCount - 1, 2) = cl.Offset(, 1).Value
End With
Case Is < Date
With Me.ListBox2
.AddItem cl.Offset(0, -1).Value
.List(.ListCount - 1, 1) = Format(cl.Value, "dd mmm yyyy")
.List(.ListCount - 1, 2) = cl.Offset(, 1).Value
End With
End Select
Next cl
End Sub

[RonNCmale]

Thanks guys, works

[snb]

As I told you before; this suffices.
Private Sub UserForm_Initialize()
ListBox1.List = Sheet1.Cells(1).CurrentRegion.Value
ListBox2.List = ListBox1.List

For j = ListBox1.ListCount - 1 To 1 Step -1
If ListBox1.List(j, 1) <> Date Then ListBox1.RemoveItem j
If ListBox2.List(j, 1) = Date Then ListBox2.RemoveItem j
Next
End Sub

You can set the property Columncount: 3 in 'design mode'
So only the first 3 columns will be visible , although the listbox contains 4 columns of data.