VBA Changes Date Format

[FlashNZ]

Hi,

I have some VBA that allows me to edit data via a Userform, it then saves the changes to a range in a worksheet. The trouble is that when I save the changes to the date field it changes the date format from dd/mm/yyy to mm/dd/yyyy, e.g from 02/11/2020 to 11/02/2020. The Value that contains the Date is Emp2.

How do I fix this?

Private Sub cmdEdit_Click()
'declare the variables
Dim findvalue As Range
Dim cNum As Integer
Dim DataSH As Worksheet
'error handling
On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
Set DataSH = Sheet1
'check for values
If Emp1.Value = "" Or Emp2.Value = "" Then
MsgBox "There is not data to edit"
Exit Sub
End If
'clear the listbox
lstEmployee.RowSource = ""
'find the row to edit
Set findvalue = DataSH.Range("B:B"). _
Find(What:=Me.Emp1.Value, LookIn:=xlValues, LookAt:=xlWhole)
'update the values
findvalue = Emp1.Value
findvalue.Offset(0, 1) = Emp2.Value
findvalue.Offset(0, 2) = Emp3.Value
findvalue.Offset(0, 3) = Emp4.Value
findvalue.Offset(0, 4) = Emp5.Value
findvalue.Offset(0, 5) = Emp6.Value
findvalue.Offset(0, 6) = Emp7.Value
findvalue.Offset(0, 7) = Emp8.Value
findvalue.Offset(0, 8) = Emp9.Value
findvalue.Offset(0, 9) = Emp10.Value
findvalue.Offset(0, 10) = Emp11.Value
'unprotect the worksheets for the advanced filter
'Unprotect_All
'filter the data
DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data!$Q$8:$Q$9"), CopyToRange:=Range("Data!$S$8:$AC$8"), _
Unique:=False
'if no data exists then clear the rowsource
If DataSH.Range("S9").Value = "" Then
lstEmployee.RowSource = ""
Else
'add the filtered data to the rowsource
lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
End If
'return to sheet
Sheet2.Select
'Protect all sheets
'Protect_All
'error block
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets
'Protect_All
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & _
"The error number is: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[Marc L]

​

As VBA knows only the US date format, if your Windows Regional date setting is not the same format​

so just use the cell property FormulaLocal when allocating the local date …​

​

Another way is to convert the text as a date via the VBA function CDate …​

 

[FlashNZ]

​

As VBA knows only the US date format, if your Windows Regional date setting is not the same format​

so just use the cell property FormulaLocal when allocating the local date …​

​

Another way is to convert the text as a date via the VBA function CDate …​

Thanks for the quick reply and I thought it might have something to do with regional settings, I'm in New Zealand so we don't use the US format.

Any idea how I fit that into my code?

 

[Marc L]

​

As both ways work, try CDate function for example …​

 

[FlashNZ]

IK'm new to VBA, how do I use the CDate function in my code?