Adding dates to ComboBox

[SteIT]

I am tring to add dates (today & tomorrow) to a combobox.

Private Sub userform_Initialize()
today = Date
tomorrow = today + 1

ComboBox1.AddItem today
ComboBox1.AddItem tomorrow
End sub

The first date is showm as 30/4/08, second date is shown as 5/1/2008. Second date should be 1/5/2008 for me in the UK.

Not sure if this is a problem with excel or vba ?

Any help would be appreciated.

 

[SkipVought]

Hi,

You are using the British d/m/yyyy format. Excel defaults to d/m/yyyy.

Since the combobox is storing TEXT (not a real date), and you probably want the dates stored in d/m/yyyy format, I'd use the Format function to load. BUT, the problem will come when you select and string and need to CONVERT to a date. The DateValue or CDate functions assume the Excel default d/m/yyyy, so you will need to parse the values into the DateSerial function.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shVBA]

I have written two snippets of code one to convert the date to a string and another one to convert back to a date, im not sure if this is exactly what you want but:

Today = FormatDateTime(Now(), vbShortDate)
Tomorrow = FormatDateTime(Now() + 1, vbShortDate)

Today = CStr(Today)
Tomorrow = CStr(Tomorrow)

ComboBox1.AddItem Today
ComboBox1.AddItem Tomorrow

This code is to populate the combobox, on the userform initialise.

Dim SDate As Date

SDate = ComboBox1.Value

SDate = CDate(SDate)

This converts back to a date.

 

[SkipVought]

shVBA

Dim SDate As Date

SDate = ComboBox1.Value

SDate = CDate(SDate)

"This converts back to a date."

NOT ALWAYS. Depends on the STRING format!!!

BTW, nothing happens on the last statement in your code, as the CONVERSION occurs from TEXT, in the control, to DATE as you have declared the variable, in the SECOND statement.

The issue is as the OP stated -- STRING "1/5/2008" becomes DATE 5/1/2008, as I have already explained.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]