Trouble With Dates!

[longytravel]

Afternoon,

As ever help would be greatly recieved!

I have this code from one of my userforms. It populates the detail from the form onto a sheet.

The code at the bottom populates me the date in the relevant text box. It shows in the format of dd.mm.yy which is what i am after as an end result.

when it imports it into the document it changes it to mm.dd.yy.

I've played around with the formatting but no joy

Any ideas?

Thanks

Private Sub CommandButton1_Click()
Me.ComboBox2.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox2.SetFocus
End Sub
Private Sub CommandButton2_Click()
Dim eRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'check for a name
If Trim(Me.ComboBox2.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please Enter A Project"
Exit Sub
End If
'copy the data to the database
ws.Cells(eRow, 1).Value = Me.ComboBox2.Value
ws.Cells(eRow, 2).Value = Me.TextBox6.Value
ws.Cells(eRow, 3).Value = Me.TextBox1.Value
ws.Cells(eRow, 4).Value = Me.TextBox2.Value
ws.Cells(eRow, 5).Value = Me.TextBox3.Value
ws.Cells(eRow, 6).Value = Me.TextBox4.Value
ws.Cells(eRow, 7).Value = Me.TextBox5.Value
ws.Cells(eRow, 8).Value = Me.ComboBox4.Value
ws.Cells(eRow, 9).Value = Me.ComboBox5.Value
 
'clear the data
Me.ComboBox2.Value = ""
Me.TextBox6.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox2.SetFocus
 
MsgBox "Thank You!  Your Update Has Been Saved!", vbInformation + vbOKOnly, "Update Complete"
ActiveWorkbook.Close True
End Sub
Private Sub CommandButton3_Click()
Application.Visible = True
UserForm1.Hide
Dim t_Range As Excel.Range
Set t_Range = ActiveSheet.Range("Data")
'--- Could use any number of methods to get the range...
t_Range.Borders(xlDiagonalDown).LineStyle = xlNone
t_Range.Borders(xlDiagonalUp).LineStyle = xlNone
With t_Range.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With t_Range.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With t_Range.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With t_Range.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
t_Range.Borders(xlInsideVertical).Weight = xlThin
t_Range.Borders(xlInsideHorizontal).Weight = xlThin
 
End Sub
 
 
Private Sub TextBox6_Change()
End Sub
Private Sub UserForm_Initialize()
   Me.TextBox6.Text = Date
End Sub

 

[VoG]

See if this thread from earlier helps http://www.mrexcel.com/forum/showthread.php?t=569217

 

[longytravel]

Thanks VoG

New to all this - where and how would this fit in my code?

 

[VoG]

Which line of your code writes a date to a sheet?

 

[longytravel]

ws.Cells(eRow, 2).Value = Me.TextBox6.Value

This bit i think!!!!

THe key is to make sure as more entries are added they all follow after one another and not write over each other

Cheers

 

[VoG]

Try

ws.Cells(eRow, 2).Value = DateValue(Me.TextBox6.Value)

 

[longytravel]

You are the man!
THanks!