UK date format and combo box

[Guest]

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

 

[Ron de Bruin]

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

 

[keepITcool]

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 :