| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
| Convert date from text format to date format | Excel Discussion (Misc queries) | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
| Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming | |||
Linear Mode
