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

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

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

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

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