DD/MM/YY format from a userform textbox excel 2007

[NeilD]

Hi All,

I am trying to upload information from a userform into another spreadsheet, the first two textboxes are fine as they are text, however I would like the third text box, named date1 to be used for dates in DD/MM/YY format, currently, no matter how I try to format the recieving cells it will appear in MM/DD/YY, this then throws out my formulae in that spreadsheet.

I am using the following code at present, could someone please help fill in the code I need for this to force the format of the textbox.

Thanks in advance
NeilD

Private Sub CommandButton1_Click()
Workbooks.Open ("L:\QA\KE02 Training\07 Training matrices\TRAINING MATRICES\09 Cross Training Matrix.xls")
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")
Worksheets("Database").Unprotect Password:="matrix"
ActiveSheet.Range("$A$1:$E$1399").AutoFilter Field:=1
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Value = Me.name1.Value
ws.Cells(iRow, 2).Value = Me.dept1.Value
ws.Cells(iRow, 3).Value = Me.date1.Value
Me.name1.Value = ""
Me.dept1.Value = ""
Me.date1.Value = ""
Me.name1.SetFocus
Worksheets("Database").Range("$A$1:$e$1399").AutoFilter Field:=1, Criteria1:="<>"
Worksheets("Database").Protect Password:="matrix", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Worksheets("Database").EnableAutoFilter = True
ActiveWorkbook.Close SaveChanges:=True

End Sub
Private Sub CommandButton24_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the exit button"
End If

 

[RoryA]

Try using CDate:

ws.Cells(iRow, 3).Value = CDate(Me.date1.Value)

 

[NeilD]

That works a treat. Thanks very much