Text box date format
 

Does anyone know how you can change american date format to UK englishwhen a
text box = a cell with a date, it displays it in US format.

Text box date format
 

TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK englishwhen
a text box = a cell with a date, it displays it in US format.

Text box date format
 

Thanks Bob. I put this in the code for the text box but it hasn't worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US format.

Text box date format
 

Hi Jim,

I am assuming that you require the answer as it is done in VBA code. Is that
correct? If so then the following examples should explain it.

'Date in range A3 set to 3 Aug 2008

'Following displays 8/3/2008 in textbox (US format)
Sheet1.TextBox1.Value = Sheet1.Range("F3")

'Following displays 03 Aug 2008 in textbox
Sheet1.TextBox1.Value = Format(Range("F3"), "dd mmm yyyy")

'Following displays 3/8/2008 in textbox
Sheet1.TextBox1.Value = Format(Range("F3"), "d/mm/yyyy")


If you set a linked cell in the TextBox properties then the Linked Cell must
contain the formatting so that it is a text value not a date value like the
following

=TEXT(DATEVALUE("3 Aug 2008"),"dd mmm yyyy")


In the above formula, DATEVALUE("3 Aug 2008") could simply reference a cell
with an actual date like the following. (Assuming that G3 contains the date 3
Aug 2008)

=TEXT(G3,"dd mmm yyyy")

Note that TEXT function is used on a worksheet and FORMAT function is used
in VBA to achieve the same results.



--
Regards,

OssieMac


"Jim Lavery" wrote:

Thanks Bob. I put this in the code for the text box but it hasn't worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US format.

Text box date format
 

An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

Do you have the TextBox linked to the cell via the LinkedCell property? If
so, try unlinking the TextBox (clear the LinkedCell property) and using this
worksheet Change event code to control the contents of the TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute address
string for the cell you previously had the TextBox linked to. Does doing
this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

Rick,

Apologies for this but I should have mentioned that the textbox is on a form
which is diplayed on click. I assume this is why the codes haven't been
succesful.

Jim



"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you have the TextBox linked to the cell via the LinkedCell property? If
so, try unlinking the TextBox (clear the LinkedCell property) and using
this worksheet Change event code to control the contents of the TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute
address string for the cell you previously had the TextBox linked to. Does
doing this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

When you say "on a form", do you mean a UserForm or a worksheet that you use
as a "form"?

If on a worksheet, where did you get the TextBox from... the Control Toolbox
toolbar or the Drawing toolbar?

If it is on a UserForm, do you have the TextBox linked to a particular cell
via the ControlSource property?

If it is on the worksheet and from the Control Toolbox, do you have the
TextBox linked to a particular cell via the LinkedCell property

Rick


"Jim Lavery" wrote in message
...
Rick,

Apologies for this but I should have mentioned that the textbox is on a
form which is diplayed on click. I assume this is why the codes haven't
been succesful.

Jim



"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you have the TextBox linked to the cell via the LinkedCell property?
If so, try unlinking the TextBox (clear the LinkedCell property) and
using this worksheet Change event code to control the contents of the
TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute
address string for the cell you previously had the TextBox linked to.
Does doing this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

Rick,

It's a userform & the controlsource property is linked to the cell.

Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
When you say "on a form", do you mean a UserForm or a worksheet that you
use as a "form"?

If on a worksheet, where did you get the TextBox from... the Control
Toolbox toolbar or the Drawing toolbar?

If it is on a UserForm, do you have the TextBox linked to a particular
cell via the ControlSource property?

If it is on the worksheet and from the Control Toolbox, do you have the
TextBox linked to a particular cell via the LinkedCell property

Rick


"Jim Lavery" wrote in message
...
Rick,

Apologies for this but I should have mentioned that the textbox is on a
form which is diplayed on click. I assume this is why the codes haven't
been succesful.

Jim



"Rick Rothstein (MVP - VB)" wrote
in message ...
Do you have the TextBox linked to the cell via the LinkedCell property?
If so, try unlinking the TextBox (clear the LinkedCell property) and
using this worksheet Change event code to control the contents of the
TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute
address string for the cell you previously had the TextBox linked to.
Does doing this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

I'm not sure of the interaction you want from the TextBox back to the
control source cell, but assuming you only want to get the American date
from the cell when you display the UserForm for use in the code associated
with the UserForm, try this... clear the ControlSource property and put this
code line in the UserForm's Initialize event...

TextBox1.Text = Format$(Range("$G$5").Value, "dd/mm/yyyy")

When the UserForm is loaded (either the first time or subsequent to being
**unloaded** from a previous displaying), it will reach into G5 (change to
the absolute cell reference you originally used as the ControlSource), which
it always assumes is an American date, and reverses the day/month parts of
the date and puts that English date into the TextBox. If you change the
English Date in the TextBox while the UserForm is displayed and you want
that converted to an American date for display in the cell, then use this
line in the UserForm's Terminate event...

Range("$G$5").Value = CDate(Format(TextBox1.Text, "mm/dd/yyyy"))

Rick


"Jim Lavery" wrote in message
...
Rick,

It's a userform & the controlsource property is linked to the cell.

Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
When you say "on a form", do you mean a UserForm or a worksheet that you
use as a "form"?

If on a worksheet, where did you get the TextBox from... the Control
Toolbox toolbar or the Drawing toolbar?

If it is on a UserForm, do you have the TextBox linked to a particular
cell via the ControlSource property?

If it is on the worksheet and from the Control Toolbox, do you have the
TextBox linked to a particular cell via the LinkedCell property

Rick


"Jim Lavery" wrote in message
...
Rick,

Apologies for this but I should have mentioned that the textbox is on a
form which is diplayed on click. I assume this is why the codes haven't
been succesful.

Jim



"Rick Rothstein (MVP - VB)" wrote
in message ...
Do you have the TextBox linked to the cell via the LinkedCell property?
If so, try unlinking the TextBox (clear the LinkedCell property) and
using this worksheet Change event code to control the contents of the
TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute
address string for the cell you previously had the TextBox linked to.
Does doing this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.

Text box date format
 

If you have the worksheet cell formatted as dd/mm/yyyy, clear the link and
try

TextBox1.Text = Range("$G$5").Text

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Rick,

It's a userform & the controlsource property is linked to the cell.

Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
When you say "on a form", do you mean a UserForm or a worksheet that you
use as a "form"?

If on a worksheet, where did you get the TextBox from... the Control
Toolbox toolbar or the Drawing toolbar?

If it is on a UserForm, do you have the TextBox linked to a particular
cell via the ControlSource property?

If it is on the worksheet and from the Control Toolbox, do you have the
TextBox linked to a particular cell via the LinkedCell property

Rick


"Jim Lavery" wrote in message
...
Rick,

Apologies for this but I should have mentioned that the textbox is on a
form which is diplayed on click. I assume this is why the codes haven't
been succesful.

Jim



"Rick Rothstein (MVP - VB)" wrote
in message ...
Do you have the TextBox linked to the cell via the LinkedCell property?
If so, try unlinking the TextBox (clear the LinkedCell property) and
using this worksheet Change event code to control the contents of the
TextBox...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
TextBox1.Text = Format$(Target.Value, "dd/mm/yyyy")
End If
End Sub

Change the address string in the If..Then statement to the absolute
address string for the cell you previously had the TextBox linked to.
Does doing this do what you want?

Rick


"Jim Lavery" wrote in message
...
Sorry Bob I should hav ebeen more descriptive. The date remained in US
format in the text box
"Bob Phillips" wrote in message
...
An explanation of what happened/didn't happen would help.

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Thanks Bob. I put this in the code for the text box but it hasn't
worked.
"Bob Phillips" wrote in message
...
TextBox1.Text = Format(CDate(TextBox1.Text), "dd/mm/yyyy")

--
__________________________________
HTH

Bob

"Jim Lavery" wrote in message
...
Does anyone know how you can change american date format to UK
englishwhen a text box = a cell with a date, it displays it in US
format.