PDA

View Full Version : Solved: Add date to Userform



RonNCmale
02-23-2013, 03:55 AM
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.

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
see attached excel file

mikerickson
02-23-2013, 08:35 AM
Try a two column ListBox

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

RonNCmale
02-23-2013, 11:06 AM
thanks, works gr8

snb
02-23-2013, 04:20 PM
To populate listboxes/comboboxes avoid 'additem', but use 'List instead.

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

RonNCmale
02-23-2013, 05:31 PM
thanks snb,

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