| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Date Format
I have a userform that has a command button that displays a date within an
active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
Private Sub CommandButton1_Click()
Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
Hi,
Try it like this Private Sub CommandButton1_Click() If IsDate(ActiveCell.Value) Then Me.TextBox1.Value = Format(ActiveCell.Value, "dd/mm/yyyy") Else MsgBox "The active cell isn't a valid date" End If End Sub Mike "Woodi2" wrote: I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
Thanks Bob, thats great.
How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
Private Sub CommandButton1_Click()
Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... Thanks Bob, thats great. How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
You would do it like this and I still recommend you check for a valid date
Private Sub CommandButton1_Click() If IsDate(ActiveCell.Offset(0, -1).Value) Then Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd/mm/yyyy") Else MsgBox "The active cell isn't a valid date" End If End Sub Mike "Woodi2" wrote: Thanks Bob, thats great. How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
Thanks Guys, very helpful.
How do I do this when writing back to the sheet. i.e. i write back using another commandbutton as follows ActiveCell.Offset(0, -1) = TextBox1.Value How do i add into this code to change the data entry to dd/mm/yyyy. i.e. if a user enters the date in textbox 1 as 5/3/09, how can I convert that to 05/03/2009. Thanks again "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... Thanks Bob, thats great. How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Date Format
ActiveCell.Offset(0, -1) = CDate(TextBox1.Value)
and format the cell as required. -- __________________________________ HTH Bob "Woodi2" wrote in message ... Thanks Guys, very helpful. How do I do this when writing back to the sheet. i.e. i write back using another commandbutton as follows ActiveCell.Offset(0, -1) = TextBox1.Value How do i add into this code to change the data entry to dd/mm/yyyy. i.e. if a user enters the date in textbox 1 as 5/3/09, how can I convert that to 05/03/2009. Thanks again "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... Thanks Bob, thats great. How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob "Woodi2" wrote in message ... I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL VBA Coding
HI,
I have no idea about programming. but I want to learn excel VBA coding. Please help me out on this.... Bob Phillips wrote: Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell. 11-Mar-09 Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob Previous Posts In This Thread: On Wednesday, March 11, 2009 5:36 AM Woodi wrote: VB Date Format I have a userform that has a command button that displays a date within an active cell and displays this date in textbox 1. Private Sub CommandButton1_Click() Me.TextBox1.Value = ActiveCell.Value End Sub The date in the cell is in the format dd/mm/yyyy. When it is displayed in the textbox it displays it as mm/dd/yyyy. How can I change this around to the format dd/mm/yyyy? I have tried Me.TextBox1.Value = Format(Date, "dd/mm/yy") but that displays the current date, not what is in the cell. On Wednesday, March 11, 2009 5:45 AM Bob Phillips wrote: Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell. Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob On Wednesday, March 11, 2009 5:47 AM Mike wrote: Hi,Try it like thisPrivate Sub CommandButton1_Click()If IsDate(ActiveCell. Hi, Try it like this Private Sub CommandButton1_Click() If IsDate(ActiveCell.Value) Then Me.TextBox1.Value = Format(ActiveCell.Value, "dd/mm/yyyy") Else MsgBox "The active cell isn't a valid date" End If End Sub Mike "Woodi2" wrote: On Wednesday, March 11, 2009 6:36 AM Woodi wrote: Thanks Bob, thats great.How would I tie it in with thisMe.TextBox1. Thanks Bob, thats great. How would I tie it in with this Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value. Where would the code be added to change the format? Apologies but since writing it I need it to be offset 1 cell. Thanks Ian "Bob Phillips" wrote: On Wednesday, March 11, 2009 7:16 AM Bob Phillips wrote: Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell. Private Sub CommandButton1_Click() Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy") End Sub -- __________________________________ HTH Bob On Wednesday, March 11, 2009 7:16 AM Mike wrote: You would do it like this and I still recommend you check for a valid You would do it like this and I still recommend you check for a valid date Private Sub CommandButton1_Click() If IsDate(ActiveCell.Offset(0, -1).Value) Then Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd/mm/yyyy") Else MsgBox "The active cell isn't a valid date" End If End Sub Mike "Woodi2" wrote: On Wednesday, March 11, 2009 7:37 AM Woodi wrote: Thanks Guys, very helpful.How do I do this when writing back to the sheet.i.e. Thanks Guys, very helpful. How do I do this when writing back to the sheet. i.e. i write back using another commandbutton as follows ActiveCell.Offset(0, -1) = TextBox1.Value How do i add into this code to change the data entry to dd/mm/yyyy. i.e. if a user enters the date in textbox 1 as 5/3/09, how can I convert that to 05/03/2009. Thanks again "Bob Phillips" wrote: On Wednesday, March 11, 2009 7:48 AM Bob Phillips wrote: ActiveCell.Offset(0, -1) = CDate(TextBox1. ActiveCell.Offset(0, -1) = CDate(TextBox1.Value) and format the cell as required. -- __________________________________ HTH Bob Submitted via EggHeadCafe - Software Developer Portal of Choice Access Denied Errors When compiling .NET Solution http://www.eggheadcafe.com/tutorials...rors-when.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
| Convert european foreign date format to US date format | Excel Worksheet Functions | |||
| 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
