Date Format for Userform TextBox

  • Thread starter Thread starter John Calder
  • Start date Start date
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
 
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
 
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
 
Thanks Bob.....works great !

Bob Phillips said:
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
Click to expand...
 
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
 
Back
Top