MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Formatting a text box in userform


Posted by Darren on July 18, 2000 2:36 AM

Hi people !!!

I have lots of things to learn from you guys. How do I format a textbox so that it will only except date entry using dd/mmm/yyyy format. My current problem with the textbox is when a user key-in 1/12/00 (which is supposed to be 1st Dec 2000), the system will go into an infinite loop (i.e changing date from 1/12/00 to 12/1/00 and vice versa)..... system confused.

I would appreciate any help on this. Have a nice day.

Best Regards,
Darren :P

Posted by Ivan Moala on July 18, 0100 3:08 AM

I have lots of things to learn from you guys. How do I format a textbox so that it will only except date entry using dd/mmm/yyyy format. My current problem with the textbox is when a user key-in 1/12/00 (which is supposed to be 1st Dec 2000), the system will go into an infinite loop (i.e changing date from 1/12/00 to 12/1/00 and vice versa)..... system confused.


Hi Darren
The textbox doesn't have a date mask function
in VBA as in Visual Basics.....you can however
simulate one.
The following routine Assumes you have
1) A Label = Label1
2) A Textbox = Textbox1

The Lable is linked to the textbox to give you a
formated view of the date entry.
If you would like a workbook example I can send you one.

Private Sub TextBox1_Change()

TempTxt = TextBox1.Text
LTempTxt = Len(TempTxt)
x = 1

'48 TO 57 = ASCII for Numbers 0 to 9 (NB 47 = "\")
If TempTxt = "" Then GoTo Fin 'Test for empty entry

For x = 1 To LTempTxt
V = CDbl(Asc(Mid(TempTxt, x, 1)))
Wrong = Mid(TempTxt, x, 1)
If V < 47 Or V > 58 Then
msg = "The input you entered:= <" & Wrong & " >,is not valid!" & Chr(13)
msg = msg & "Valid data = a Number or /"
MsgBox msg, vbCritical, "Invalid Input"
TextBox1.Text = Left(TempTxt, x - 1)
End
End If
Next x

On Error GoTo ErrDate
If LTempTxt > 6 Then MM = TextBox1.Text
Fin:
Label1.Caption = " Date = " & Format(TextBox1.Text, "dd mmmm yyyy")

Exit Sub
ErrDate:
MsgBox "Error in Date input"
End Sub

HTH

Ivan