Hi,

i created a form whereby user will input a date and on clicking the "OK"
button on the form, this date will be posted into a cell in excel. My problem
is, when it is posted in excel, the cell is not formatted as a date. I need
it to remain as a date so that i can make use of this cell to do other
calculations. Please help. The coding of my userform is as below:

Private Sub cmdOk_Click()

Dim ws As Worksheet
Set ws = Worksheets("FORM IR37")


'copy the data to form IR37
ws.Cells(20, 12).Value = Me.txtDate.Value


'clear the data
Me.txtDate.Value = ""
Me.txtDate.SetFocus

'close the form
Unload Me

End Sub

You may find that using a calendar control makes life easier.

See Ron de Bruin's site for more info:
http://www.rondebruin.nl/calendar.htm

But you could try:

Option Explicit
Private Sub cmdOk_Click()

Dim ws As Worksheet

Set ws = Worksheets("FORM IR37")

'copy the data to form IR37
If IsDate(Me.txtDate.Value) Then
With ws.Cells(20, 12)
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.txtDate.Value)
End With
Unload Me
Else
'clear the data to start over???
Me.txtDate.Value = ""
Me.txtDate.SetFocus
End If
End Sub

But a user who enters
01/02/03
may be in for a suprise what date is used.



Dolphinv4 wrote:

Hi,

i created a form whereby user will input a date and on clicking the "OK"
button on the form, this date will be posted into a cell in excel. My problem
is, when it is posted in excel, the cell is not formatted as a date. I need
it to remain as a date so that i can make use of this cell to do other
calculations. Please help. The coding of my userform is as below:

Private Sub cmdOk_Click()

Dim ws As Worksheet
Set ws = Worksheets("FORM IR37")

'copy the data to form IR37
ws.Cells(20, 12).Value = Me.txtDate.Value

'clear the data
Me.txtDate.Value = ""
Me.txtDate.SetFocus

'close the form
Unload Me

End Sub

--

Dave Peterson