Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding dates to ComboBox

Status
Not open for further replies.

SteIT

Programmer
Nov 22, 2007
18
GB
I am tring to add dates (today & tomorrow) to a combobox.

Code:
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.
 



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]
[glasses]Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![tongue][/sub]
 
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:

Code:
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.

Code:
Dim SDate As Date

SDate = ComboBox1.Value

SDate = CDate(SDate)

This converts back to a date.
 



shVBA
Code:
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]
[glasses]Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top