Dave
Thanks for your response.
Although this format may be ambiguous, most of the people that use Excel in
my company have been entering dates in this format for the past decade so it
has effectivley become convention here to enter dates like this.
I fully agree with you that there are probably "better" ways to do this, but
I feel that trying to change convention would be a more difficult task to
achieve.
Bob's answer appears to have fixed my problem so I will go with that. Thank
you once again for your input Dave.
John
"Dave Peterson" wrote:
I've never understood how a developer could guess what date the user meant by:
01/02/03
Have you considered using an unambiguous way to get the date (multiple controls
for month, day and year) or a calendar control?
Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm
John Calder wrote:
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
--
Dave Peterson