VBA does not know the format of your initial date, so it has to guess. When
you use "06/24/06", it is clear, because there is no such thing as a 24th
month, so Format guesses correctly.
However, with "06/08/06", how does Format know which is the day and which is
the month.
You need to explicitly code the parts; day, month, year.
Look up DateSerial and other Date functions in the help. And possibly Split.

NickHK

"s_ali_hassan" <s_ali_hassan.2a7mmp_1151662206.5111@excelforum-nospam.com>
wrote in message
news:s_ali_hassan.2a7mmp_1151662206.5111@excelforum-nospam.com...
>
> i have a calendar from which a user selects the date, which is then
> displayed in a text box. The problem is that the date displayed in the
> textbox is in the format mm/dd/yyyy, when i want it to be dd/mm/yyyy.
> ive tried a few things but cannot get it to display the date correctly,
> here is my code:
>
> Private Sub Calendar1_Click()
> TextBox2 = Calendar1.Value
> TextBox2 = Format(TextBox2, "dd/mm/yyyy")
>
> End Sub
>
> my regional setting is set to english (UK).
> the properties on the calendar shows the values on the calendar as
> dd/mm/yyyy. But when i run the macro, the text box shows it
> differently.
> Also after trying to convert the format in the text box, it half works,
>
> by this i mean if i select the 24 of june 2006, it displays it as
> 24/06/2006, which is cool, but if i select 8th of june 2006, then it
> displays it as 6/8/2006, which is messed up.
>
> how can i get this to work??
> any ideas?
>
> pls help if u can
>
> thanks
>
>
> --
> s_ali_hassan
> ------------------------------------------------------------------------
> s_ali_hassan's Profile:
http://www.excelforum.com/member.php...o&userid=35325
> View this thread: http://www.excelforum.com/showthread...hreadid=557248
>