Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK date format and combo box
Another annoying UK date format question I am afraid - Using Excel 2000 and
2002 (two versions running on office PCs) I have a combo box list populated from a series in a workseet using this code : ComboBox1.List = ThisWorkbook.Worksheets("Lists").Range("A30:A395") .Value (the Range is a list of dates in format DD/MM/YYYY.) All settings are to UK date format. Yesterday the list appeared in the combobox in UK date format - today it is in US format (MM/DD/YYYY). Grateful if anyone can suggest how I can force Excel to show the list in the UK date format - and keep it that way! TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK date format and combo box
Hi L Smith
You can create a loop and fill the combo with two columns One that you see with the text value of the cells and one you hide with the value Use the value column as bound column Something like this Private Sub UserForm_Initialize() Dim varr As Variant Dim rng As Range Dim i As Long Set rng = ThisWorkbook.Sheets("sheet1").Range("A1:A10") varr = rng.Resize(, 2).Value For i = LBound(varr) To UBound(varr) varr(i, 2) = rng.Cells(i).Text Next With Me.ComboBox1 .ColumnCount = 2 .BoundColumn = 1 .ColumnWidths = 0 .List = varr .ListIndex = 0 End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "L Smith" wrote in message ... Another annoying UK date format question I am afraid - Using Excel 2000 and 2002 (two versions running on office PCs) I have a combo box list populated from a series in a workseet using this code : ComboBox1.List = ThisWorkbook.Worksheets("Lists").Range("A30:A395") .Value (the Range is a list of dates in format DD/MM/YYYY.) All settings are to UK date format. Yesterday the list appeared in the combobox in UK date format - today it is in US format (MM/DD/YYYY). Grateful if anyone can suggest how I can force Excel to show the list in the UK date format - and keep it that way! TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK date format and combo box
IF the dates are true date serials, your code will read them as date type = since VBA is US centric, they will be added to the list as USformatted strings. However if your dates were stored as strings you'd have no problem, and i think that may have happened earlier... assuming you'd like to see the same format as displayed on your worksheet you can use the cell's Text property to add the items... Private Sub UserForm_Initialize() Dim r As Range With Me.ComboBox1 ..Clear For Each r In ThisWorkbook.Worksheets("Lists").Range("A30:A395") ..AddItem r.Text Next End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam L Smith wrote : Another annoying UK date format question I am afraid - Using Excel 2000 and 2002 (two versions running on office PCs) I have a combo box list populated from a series in a workseet using this code : ComboBox1.List = ThisWorkbook.Worksheets("Lists").Range("A30:A395") .Value (the Range is a list of dates in format DD/MM/YYYY.) All settings are to UK date format. Yesterday the list appeared in the combobox in UK date format - today it is in US format (MM/DD/YYYY). Grateful if anyone can suggest how I can force Excel to show the list in the UK date format - and keep it that way! TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a combo box to show mmm-yyyy format | Excel Discussion (Misc queries) | |||
I want my combo box to return a date format instead of a number? | Excel Discussion (Misc queries) | |||
keep date format in a Combo box | Excel Worksheet Functions | |||
Date-Format of Dropdown-Combo is integer | Excel Programming | |||
Date format when using combo box | Excel Programming |