thanks Greg.

Is there a way I can change the "Long date" to show ALSO the Day (Sunday 12
November 2005) ???


"Greg Wilson" wrote in message
...
Corey,

Some points:
1. As Tom suggested, I think you should use the Initialize event instead
of
DropButtonClick. This avoids populating the cbox when the user clicks it
which may cause a flicker or slow performance. You can also eliminate this
line:
If ComboBox1.ListCount 0 Then Exit Sub
2. Range objects don't support a Date property - i.e. .Cells(myrow,
1).Date
should return an error.
3. The SpecialCells method with the argument set to xlCellTypeConstants
returns the range of nonblank cells in the column and avoids looping and
checking for nonblanks. This may offer superior performance.
4. You might want to reconsider using "dddd mmm yyyy". I get for example
"Saturday June 2004" (note missing day of the month). Perhaps the "Long
Date"
format or "dd mmm yyyy" etc. Just a suggestion.

Untested code:

Private Sub UserForm_Initialize()
Dim LastCell As Long
Dim myrow As Long
With Sheets("Sheduled In")
LastCell = .Cells(Rows.Count, "A").end(xlUp).Row
For myrow = 159 To LastCell
If .Cells(myrow, 1) < "" Then
If .Cells(myrow, 1).Value .Range("E159").Value Then
ComboBox1.AddItem Format(.Cells(myrow, 1), "Long Date")
End If
End If
Next
End With
End Sub

Alternative:-

Private Sub UserForm_Initialize()
Dim r As Range, c As Range
On Error Resume Next
With Sheets("Scheduled In")
Set r = .Range("A159:A65536")
Set r = r.SpecialCells(xlCellTypeConstants)
For Each c In r.Cells
If c.Value r(1, 1).Value Then _
ComboBox1.AddItem Format(c.Value, "dd mmmm yyyy")
Next
End With
On Error GoTo 0
Set r = Nothing: Set c = Nothing
End Sub

Regards,
Greg