Archive of Mr Excel Message Board


Back to Controls in Excel archive index
Back to archive home

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


Re: Formatting a text box in userform

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.