J
John Calder
Hi
I run Excel 2K
I have a userform that uses a textbox fot entering a date:
When I enter a date in my spreadsheet I type (for example) 2/3 an this
returns 2/Mar/2009
However when I type 2/3 in the textbox in the userform it returs 3/Feb/2009
So, the textbox interprets the 2 as the month and the 3 as the date.
This is unlike entering it directly into the spreadsheet which interprets
the 2 and the date and 3 as the month.
I would like the textbox to operate like the spreadsheet where it interprets
the 2 as the date and the 3 as the month.
This is the code I am presently using:-
Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim sEntry As String
Dim iLoc As Integer
sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc > 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err <> 0 Then
GoTo Had_Problem
End If
Exit Sub
End If
Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True
End Sub
Thanks
John
I run Excel 2K
I have a userform that uses a textbox fot entering a date:
When I enter a date in my spreadsheet I type (for example) 2/3 an this
returns 2/Mar/2009
However when I type 2/3 in the textbox in the userform it returs 3/Feb/2009
So, the textbox interprets the 2 as the month and the 3 as the date.
This is unlike entering it directly into the spreadsheet which interprets
the 2 and the date and 3 as the month.
I would like the textbox to operate like the spreadsheet where it interprets
the 2 as the date and the 3 as the month.
This is the code I am presently using:-
Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim sEntry As String
Dim iLoc As Integer
sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc > 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err <> 0 Then
GoTo Had_Problem
End If
Exit Sub
End If
Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True
End Sub
Thanks
John