Userform formatting
 

I have a UserForm textbox point to a cell on a sheet. The cell is date
formated to dd-mmm-yyyy but the test box seems to default to m/d/yyyy unless
m 12 then the format changes to d/m/yyy. Is it possible to change the date
format on the Userform or make it obey the sheet formatting?

--
Trefor

Userform formatting
 

break the assignment of controlsource and
load the textbox with code. Then you can format it just like the cell

Private Sub Userform_Initialize()
Textbox1.Value = worksheets("Sheet1").Range("A1").Text
End Sub

--
Regards,
Tom Ogilvy


"Trefor" wrote:


I have a UserForm textbox point to a cell on a sheet. The cell is date
formated to dd-mmm-yyyy but the test box seems to default to m/d/yyyy unless
m 12 then the format changes to d/m/yyy. Is it possible to change the date
format on the Userform or make it obey the sheet formatting?

--
Trefor

Userform formatting
 

Tom,

Thanks for the reply, that does indeed fix one problem but breaks another. I
would like to edit the text box on the userform and have it update the cell
on the spreedsheet. Now that I have made you selected change it does not
update the cell anymore. Sorry I am new to Userforms so this is probably
really simple!

--
Trefor


"Tom Ogilvy" wrote:

break the assignment of controlsource and
load the textbox with code. Then you can format it just like the cell

Private Sub Userform_Initialize()
Textbox1.Value = worksheets("Sheet1").Range("A1").Text
End Sub

--
Regards,
Tom Ogilvy


"Trefor" wrote:


I have a UserForm textbox point to a cell on a sheet. The cell is date
formated to dd-mmm-yyyy but the test box seems to default to m/d/yyyy unless
m 12 then the format changes to d/m/yyy. Is it possible to change the date
format on the Userform or make it obey the sheet formatting?

--
Trefor

Userform formatting
 

You would use the event code of the textbox and write the change back to the
sheet.

Private Sub TextBox1_Change()
Dim dt As Date, rng As Range
Set rng = Worksheets("Sheet1").Range("A1")
If IsDate(TextBox1.Text) Then
On Error Resume Next
dt = CDate(TextBox1.Text)
rng.Value = dt
On Error GoTo 0
End If
End Sub

this would go in the userform module.
--
Regards,
Tom Ogilvy


"Trefor" wrote:

Tom,

Thanks for the reply, that does indeed fix one problem but breaks another. I
would like to edit the text box on the userform and have it update the cell
on the spreedsheet. Now that I have made you selected change it does not
update the cell anymore. Sorry I am new to Userforms so this is probably
really simple!

--
Trefor


"Tom Ogilvy" wrote:

break the assignment of controlsource and
load the textbox with code. Then you can format it just like the cell

Private Sub Userform_Initialize()
Textbox1.Value = worksheets("Sheet1").Range("A1").Text
End Sub

--
Regards,
Tom Ogilvy


"Trefor" wrote:


I have a UserForm textbox point to a cell on a sheet. The cell is date
formated to dd-mmm-yyyy but the test box seems to default to m/d/yyyy unless
m 12 then the format changes to d/m/yyy. Is it possible to change the date
format on the Userform or make it obey the sheet formatting?

--
Trefor

Userform formatting
 

Tom,

You have been very helpful thankyou very much.

--
Trefor


"Tom Ogilvy" wrote:

You would use the event code of the textbox and write the change back to the
sheet.

Private Sub TextBox1_Change()
Dim dt As Date, rng As Range
Set rng = Worksheets("Sheet1").Range("A1")
If IsDate(TextBox1.Text) Then
On Error Resume Next
dt = CDate(TextBox1.Text)
rng.Value = dt
On Error GoTo 0
End If
End Sub

this would go in the userform module.
--
Regards,
Tom Ogilvy


"Trefor" wrote:

Tom,

Thanks for the reply, that does indeed fix one problem but breaks another. I
would like to edit the text box on the userform and have it update the cell
on the spreedsheet. Now that I have made you selected change it does not
update the cell anymore. Sorry I am new to Userforms so this is probably
really simple!

--
Trefor


"Tom Ogilvy" wrote:

break the assignment of controlsource and
load the textbox with code. Then you can format it just like the cell

Private Sub Userform_Initialize()
Textbox1.Value = worksheets("Sheet1").Range("A1").Text
End Sub

--
Regards,
Tom Ogilvy


"Trefor" wrote:


I have a UserForm textbox point to a cell on a sheet. The cell is date
formated to dd-mmm-yyyy but the test box seems to default to m/d/yyyy unless
m 12 then the format changes to d/m/yyy. Is it possible to change the date
format on the Userform or make it obey the sheet formatting?

--
Trefor