i have a calendar from which a user selects the date, which is the
displayed in a text box. The problem is that the date displayed in th
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 a
dd/mm/yyyy. But when i run the macro, the text box shows i
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 a
24/06/2006, which is cool, but if i select 8th of june 2006, then i
displays it as 6/8/2006, which is messed up.

how can i get this to work??
any ideas?

pls help if u can

thank

--
s_ali_hassa
-----------------------------------------------------------------------
s_ali_hassan's Profile: http://www.excelforum.com/member.php...fo&userid=3532
View this thread: http://www.excelforum.com/showthread.php?threadid=55724

I think I can help he

I messed around for a while and came up with this, which seems to work.

Private Sub CommandButton1_Click()
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy;@")
End Sub

thanks for that,

well i tried it, but it is still the same. If you choose any date abov
the 12th then the date is displayed correctly, But if u choose a dat
with the day less than or equal to the 12 then it is displayed a
mm/dd/yyyy.

very confusing (and irritating!)

any more ideas?

--
s_ali_hassa
-----------------------------------------------------------------------
s_ali_hassan's Profile: http://www.excelforum.com/member.php...fo&userid=3532
View this thread: http://www.excelforum.com/showthread.php?threadid=55724

Hello Ali Hassan

what about this one?
I'm not used to calendars in excel but if Calendar1.Value returns a
date format, this should work.

Private Sub Calendar1_Click()
TextBox2 = Format(Calendar1.Value, "dd/mm/yyyy")
End Sub

Ingolf

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"
wrote in message
news:s_ali_hassan.2a7mmp_1151662206.5111@excelforu m-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

nice one ingolf.
seems like that works for now!


--
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

Whenever I don't like the way the Format functions are working, I pull
everthing apart and then put it back together EXACTLY the way I want it to be.

In your case I would use this:

Dim Month as Integer
Dim Year as Integer
Dim Day as Integer
Dim TheFinalProduct as String

Month = VBA.Month(Calendar1.Value)
Year = VBA.Year(Calendar1.Value)
Day = VBA.Day(Calendar1.Value)

TheFinalProduct = Day & "/" & Month & "/" & Year
TextBox2 = TheFinalProduct

Try It, this comes in handy for me all the time.

"s_ali_hassan" wrote:


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