Jomathr
11-20-2013, 04:10 PM
I have an excel vba file that is used in multiple country and on spanish system the code I have using the DATESERIAL return a custom error that I created to inform the user. I am wondering if the behavior of DATESERIAL differ depending on regional settings of the computer since it return a date.
here is the code I use:
Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim langue As String
Dim LastDay As Date
Dim FirstDay As Date
Dim test3 As Integer
FirstDay = Range("AcctingPeriod").Value + 3 & "/01/" & Range("FiscalYear").Value - 1
LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0) ' last day of the month
langue = Worksheets("Config").Range("Language")
With Me.TxtDate.Value
If Range("AcctingPeriod").Value + 4 > 12 Then
FirstDay = Range("AcctingPeriod").Value - 9 & "/01/" & Range("FiscalYear").Value
LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0) ' last day of the month
If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
Else
MsgBox Worksheets(langue).Range("DateError")
Cancel = True
End If
Else
If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
Else
MsgBox Worksheets(langue).Range("DateError")
Cancel = True
End If
End If
End With
End Sub
if the DATESERIAL depend on regional settings is there a way around it to check the date entered using either Format() or tweaking the code to accept any date format. The default format I use in the textbox is YYYY/MM/DD.
Hope someone encountered this before.
Thank you in advance for your time
here is the code I use:
Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim langue As String
Dim LastDay As Date
Dim FirstDay As Date
Dim test3 As Integer
FirstDay = Range("AcctingPeriod").Value + 3 & "/01/" & Range("FiscalYear").Value - 1
LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0) ' last day of the month
langue = Worksheets("Config").Range("Language")
With Me.TxtDate.Value
If Range("AcctingPeriod").Value + 4 > 12 Then
FirstDay = Range("AcctingPeriod").Value - 9 & "/01/" & Range("FiscalYear").Value
LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0) ' last day of the month
If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
Else
MsgBox Worksheets(langue).Range("DateError")
Cancel = True
End If
Else
If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
Else
MsgBox Worksheets(langue).Range("DateError")
Cancel = True
End If
End If
End With
End Sub
if the DATESERIAL depend on regional settings is there a way around it to check the date entered using either Format() or tweaking the code to accept any date format. The default format I use in the textbox is YYYY/MM/DD.
Hope someone encountered this before.
Thank you in advance for your time