Combobox Date format change

[Corey]

I have a combobox on a u/form that lists date in a dd/mm/yy format from the sheet they are in.
How can i change the way they appear int he Combobox to dddd mmmm yyyy instead without having the actual value in the sheet changed ?

Corey....

 

[Tom Ogilvy]

Load the combobox with AddItem by looping through the cells and adding them
with code to the combobox in the initialize event instead of using the
rowsource property.

--
Regards,
Tom Ogilvy

I have a combobox on a u/form that lists date in a dd/mm/yy format from the
sheet they are in.
How can i change the way they appear int he Combobox to dddd mmmm yyyy
instead without having the actual value in the sheet changed ?

Corey....

 

[Corey]

Tom, I am already doing that.
But the cells are formatted as : dd/mm/yy.

I want the Combobox to display dddd mmmm yyyy.

Code i am using below :

Private Sub ComboBox1_DropButtonClick()
If ComboBox1.ListCount > 0 Then Exit Sub
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Scheduled In").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("Scheduled In")
..Select
For myrow = 159 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Date > Sheets("Scheduled In").Range("E159").Date
Then
ComboBox1.AddItem Cells(myrow, 1)
End If
End If
Next
End With
End Sub

Corey....

 

[Guest]

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

 

[Corey]

thanks Greg.

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

 

[Guest]

You could just specify this format: "dddd MMMM dd yyyy"

To answer your question, the "Short Date" and "Long Date" formats are set
through the Control Panel. FWIW, my take on these formats is that they allow
developers to specify one or the other format types (Long or Short) in their
programs as opposed to a specific format. This enables the computer owner to
control the format and maintain consistency while running multiple programs
(particularly important for a business).

For Windows 2000 Profressional:
Control Panel > Regional Options > Date tab
In the Long Date window specify: dddd MMMM dd yyyy

For Windows XP Professional:
Control Panel > Regional and Language Options > Regional Options tab
Click the Customize button > Date tab...

Regards,
Greg

 

[Corey]

Thank you Greg.
Perfectly done.