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

NeilD

New Member
Joined
Mar 4, 2009
Messages
4
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,369
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Try using CDate:
Code:
ws.Cells(iRow, 3).Value = CDate(Me.date1.Value)
 
Upvote 0

Forum statistics

Threads
1,190,916
Messages
5,983,572
Members
439,850
Latest member
suhailrocks786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top