Solved: Add date to Userform

[RonNCmale]

I have a excel spreadsheet that when I check for due/overdue a userform comes up with the list of names. I need to add dates beside the names when it comes up.

[vba]Private Sub UserForm_Initialize()
Dim rDates As Range

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
Me.ListBox1.AddItem cl.Offset(0, -1).Value

Case Is < Date
Me.ListBox2.AddItem cl.Offset(0, -1).Value

End Select
Next cl
End Sub[/vba]
see attached excel file

[mikerickson]

Try a two column ListBox

[VBA]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[/VBA]

[RonNCmale]

thanks, works gr8

[snb]

To populate listboxes/comboboxes avoid 'additem', but use 'List instead.

[VBA]Private Sub UserForm_Initialize()
ListBox1.List = Sheet1.Cells(1).CurrentRegion.Offset(1).Value
ListBox2.List = ListBox1.List

For j = ListBox1.ListCount - 2 To 0 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[/VBA]

[RonNCmale]

thanks snb,

This also works gr8, and thanks for the lesson of List instead of additems