| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format problem??
Hi there, hope you can help with this,,,
am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then Me.TextBox1.SetFocus MsgBox "Please enter an Amount" Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus Unload Me End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
What do you want in that cell:
ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then Me.TextBox1.SetFocus MsgBox "Please enter an Amount" Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus Unload Me End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
On Dec 27, 8:28*pm, Dave Peterson wrote:
What do you want in that cell: ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ * .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then * Me.TextBox1.SetFocus * MsgBox "Please enter an Amount" * Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus * * Unload Me End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - sorry dave, because I want to be able to have the date that is entered into the textbox placed into the cell, but in the format dd/mm/yy hych |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
On Dec 27, 8:28*pm, Dave Peterson wrote:
What do you want in that cell: ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ * .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then * Me.TextBox1.SetFocus * MsgBox "Please enter an Amount" * Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus * * Unload Me End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - Am looking for the date entered into the textbox to be placed into a cell in the following format 'dd/mm/yy' was hoping this would work, but from your replied question i guess i am asking if the cell contains the date in the format specified, have adjusted to the following but still no luck, ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value = Date, "dd/mm/yy") Steve |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
maybe...
ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value, "dd/mm/yy") But I'd use: with ws.Cells(iRow, 2) .numberformat = "dd/mm/yy" .Value = Me.TextBox2.Value end with This assumes that textbox2 actually contains a date. Steve wrote: On Dec 27, 8:28 pm, Dave Peterson wrote: What do you want in that cell: ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then Me.TextBox1.SetFocus MsgBox "Please enter an Amount" Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus Unload Me End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - Am looking for the date entered into the textbox to be placed into a cell in the following format 'dd/mm/yy' was hoping this would work, but from your replied question i guess i am asking if the cell contains the date in the format specified, have adjusted to the following but still no luck, ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value = Date, "dd/mm/yy") Steve -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
On Dec 27, 9:29*pm, Dave Peterson wrote:
maybe... ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value, "dd/mm/yy") But I'd use: with ws.Cells(iRow, 2) * .numberformat = "dd/mm/yy" * .Value = Me.TextBox2.Value end with This assumes that textbox2 actually contains a date. Steve wrote: On Dec 27, 8:28 pm, Dave Peterson wrote: What do you want in that cell: ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ * .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then * Me.TextBox1.SetFocus * MsgBox "Please enter an Amount" * Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus * * Unload Me End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - Am looking for the date entered into the textbox to be placed into a cell in the following format *'dd/mm/yy' was hoping this would work, but from your replied question i guess i am asking if the cell contains the date in the format specified, have adjusted to the following but still no luck, ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value = Date, "dd/mm/yy") Steve -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks dave, That does allow it in the right format, but is placing in the cell as a text value, can this be avoided? i have other cells that calculate from this date and would be helpful if it is possible! Steve |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format problem??
If it's putting the value in as text, then the "date" in the textbox isn't
really a date to excel. Just because you think 31/12/2007 is a date, it doesn't mean that excel does. You could struggle with parsing the date, but you could still mess up and reverse the month and day that the user really intends. You may want to drop the textbox and replace it with 3 comboboxes (month, day and year) or even a calendar control--so you don't have to worry about misinterpretting the input as the wrong date. Ron de Bruin shares calendar control instructions he http://www.rondebruin.nl/calendar.htm Steve wrote: On Dec 27, 9:29 pm, Dave Peterson wrote: maybe... ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value, "dd/mm/yy") But I'd use: with ws.Cells(iRow, 2) .numberformat = "dd/mm/yy" .Value = Me.TextBox2.Value end with This assumes that textbox2 actually contains a date. Steve wrote: On Dec 27, 8:28 pm, Dave Peterson wrote: What do you want in that cell: ws.Cells(iRow, 2).Value = Me.TextBox2.Value or ws.Cells(iRow, 2).Value = Format(Date, "dd/mm/yy") Steve wrote: Hi there, hope you can help with this,,, am using the code below with few problems, the following line is showing in my spreadsheet as "TRUE" rather than the date that gets entered ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") any help would be appreciated Steve Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Lesson1") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.TextBox1.Value) = "" Then Me.TextBox1.SetFocus MsgBox "Please enter an Amount" Exit Sub End If ws.Cells(iRow, 1).Value = Me.TextBox1.Value ws.Cells(iRow, 2).Value = Me.TextBox2.Value = Format(Date, "dd/mm/yy") ws.Cells(iRow, 3).Value = Me.TextBox3.Value ws.Cells(iRow, 5).Value = Me.TextBox4.Value ws.Cells(iRow, 6).Value = Me.TextBox5.Value Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" Me.TextBox5.Value = "" Me.TextBox1.SetFocus Unload Me End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - Am looking for the date entered into the textbox to be placed into a cell in the following format 'dd/mm/yy' was hoping this would work, but from your replied question i guess i am asking if the cell contains the date in the format specified, have adjusted to the following but still no luck, ws.Cells(iRow, 2).Value = Format(Me.TextBox2.Value = Date, "dd/mm/yy") Steve -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks dave, That does allow it in the right format, but is placing in the cell as a text value, can this be avoided? i have other cells that calculate from this date and would be helpful if it is possible! Steve -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date format problem | Excel Worksheet Functions | |||
| Date Format Problem | Excel Discussion (Misc queries) | |||
| Date format problem | Excel Worksheet Functions | |||
| Date Format problem | Excel Discussion (Misc queries) | |||
| date format problem | Excel Discussion (Misc queries) | |||
![Steve[_9_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
