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.