Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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 "Corey" wrote in message ... 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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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.... "Tom Ogilvy" wrote in message ... 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 "Corey" wrote in message ... 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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Date format change
Thank you Greg.
Perfectly done. "Greg Wilson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Date ComboBox | Excel Discussion (Misc queries) | |||
Format date in combobox | Excel Programming | |||
Combobox/Rowsource - loses date/time format | Excel Programming | |||
ComboBox Date Format Problem | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |