Strange Calendar control formatting issue

[philfloyduk]

Hi.

I have a repair booking UserForm that uses Calendar control 12.0. I've used the code below to format it to dd/mm/yyyy which works if you're selecting a date within the current month, but not if you're selecting a date from another month. For example, with today being 24th Feb, if I select 25/02/2011 it'll format correctly. However, if I select 01/03/2011 (which is likely to happen toward the end of the month) it won't format it.

Thanks in advance for any help offered

Private Sub Calendar1_Click()
appdateTextBox.Value = Calendar1.Value
appdateTextBox.Text = Format(appdateTextBox.Text, "dd/mm/yyyy")
Calendar1.Visible = False
apptimeTextBox.SetFocus
End Sub

 

[mole999]

try making month mmm not mm 1/3 and 3/1 look different, but it will never mistake Jan for Mar

 

[philfloyduk]

Thanks for your reply.

I've tried your method but I just get the name of the month in short display. So if I select the 10th March I get 3-Oct-2011, but the dates in Feb are now also the wrong way around.

 

[ZVI]

Replace 2 lines of code:
appdateTextBox.Value = Calendar1.Value
appdateTextBox.Text = Format(appdateTextBox.Text, "dd/mm/yyyy")

by this single line:
appdateTextBox.Value = Format(Calendar1.Value, "dd/mm/yyyy")

 

[philfloyduk]

Problem solved!

Thanks very much


Phil

 

[ZVI]

Glad it helped, Phil.

Excel treats the date, which is in the text format, using its own sense in recognition of day and month.
But if date value is in the date (i.e. numeric) format, then it never confuse the day with month.
The calendar control returns value in the date (numeric) format, thus the direct formatting of that value is correct always.

Kind Regards,
Vlad