Date Format for Userform TextBox

[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

 

[Bob Phillips]

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

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

 

[Dave Peterson]

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]

Thanks Bob.....works great !

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

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



--
__________________________________
HTH

Bob

 

[John Calder]

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