CODEWITHSUNDEEP
vbadateexcel
lowak
lowak

Reputation: 1284

Change date's format after loading it to UserForm textbox

I have a userform with textbox. When textbox is initialized it's getting filled with actual date. What I want to do is to fill it with custom date format = DD-MM-YYYY

I wrote code below and something is wrong about it but I have no idea what is wrong. Code has msgbox before inserting value, MsgBox shows date in a custom format but when it is passed to textbox.value it's like M/DD/YYY.

Dim year As Long, year_control As Date

year = Format(Date, "yyyy")
year_control = Format(Date, "dd-mm-yyyy")

MsgBox (year_control)

textbox.Value = year_control

(...)

If year_control < "01-04-" & year Then
    Me.Controls("rok1").Value = True
Else
    Me.Controls("rok2").Value = True
End If

Upvotes: 1

Views: 12957

Answers (1)

user2140261
user2140261

Reputation: 7993

You cannot "Format" a date variable: 

year_control As Date
year_control = Format(Date, "dd-mm-yyyy")

The above code does nothing because a Date variable is simply holing a date more specifically VBA stores Date variables as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.

No matter what you do to this variable it will always display dates according to the short date format recognized by your computer. Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.

So while you can change the internal value that is held by the Date Variable you cannot store its format inside of the same vairable.

You can however display it however you would like inside of a string variable. So, if you used: 

Dim year As Long, year_control As Date
Dim strYear_control As string


year = Format(Date, "yyyy")
year_control = Format(Date, "dd-mm-yyyy")
strYear_control = Format(year_control , "dd-mm-yyyy")
MsgBox (strYear_control)

textbox.Value = strYear_control

It should work as you are expecting. As the Format() function will return a Variant (String) containing an expression formatted according to instructions contained in a format expression.

As a side note you may also wish to use 

Format$(year_control , "dd-mm-yyyy")

as it will be much faster, You also can use FormatDateTime to format your date in other various ways.

Upvotes: 1

Related Questions