Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting and userform | Excel Discussion (Misc queries) | |||
Formatting userform textbox - help required | Excel Programming | |||
Formatting number in a UserForm TextBox | Excel Programming | |||
Userform/Cell formatting | Excel Programming | |||
Userform Formatting | Excel Programming |