date format problem??

S

Steve

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
 
D

Dave Peterson

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")
 
S

Steve

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")













--

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
 
S

Steve

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")













--

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
 
D

Dave Peterson

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.
 
S

Steve

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.









--

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
 
D

Dave Peterson

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 here:
http://www.rondebruin.nl/calendar.htm
 
S

Steve

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 here:http://www.rondebruin.nl/calendar.htm











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks dave,

Was thinking of using a calendar control, but how would i go about
having the date placed in the right position as i am using the iRow to
place information into the next available row?

any help with some code would be appreciated.

Many thanks for your assistance with this query Dave.



Steve
 
S

Steve

Thanks dave,

Was thinking of using a calendar control, but how would i go about
having the date placed in the right position as i am using the iRow to
place information into the next available row?

any help with some code would be appreciated.

Many thanks for your assistance with this query Dave.

Steve- Hide quoted text -

- Show quoted text -

Dave would the following work? if it was placed into the userform
instead of the 'me.textbox2.value'

ws.Cells(iRow, 3).Value = Calendar1.Value

have used calendar controls before but not in this way, only actually
in the open spreadsheet range

Steve
 
S

Steve

Dave would the following work? if it was placed into the userform
instead of the 'me.textbox2.value'

ws.Cells(iRow, 3).Value = Calendar1.Value

have used calendar controls before but not in this way, only actually
in the open spreadsheet range

Steve- Hide quoted text -

- Show quoted text -

Dave,

dont worry,

the following worked fine

Private Sub Calendar1_Click()
tdealsfrm.TextBox2.Value = Calendar1.Value
Unload Me
End Sub


Again, many thanks for your assistance



Steve
 
D

Dave Peterson

I think I'd use a label instead of a textbox. And I'd use an unambiguous date
format (spell out the month).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top