VBA Question: User Form and input masks

[MarianneR]

Hi!

I've created a User Form for people who are not experienced with Excel
The form includes text boxes as well as pull-downs (combo boxes).
was wondering if there was a way to use input masks (like in Access) t
prevent uses from entering inappropriate data.

For example, I have several date fields. Is it possible to have m
user form already have a mm/dd/yyyy format for them? Or, if that's no
possible, is there some way to make the User Form beep at them? I hav
prompts in the labels next to the boxes, but I'm afraid it won't b
enough.

The people on this forum have been so helpful. Thank you very much.

Sincerely,
Marianne :

 

[Bob Phillips]

Marianne,

Date input masks are tricky.

I really had this correspondence with another poster that may help you

http://www.vbaexpress.com/forum/showthread.php?t=5537&highlight=keypress

and also this one

http://tinyurl.com/9f9dg

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

No, there is no built in support for it. If you want to use code to
validate the input using the the change event or exit event, then that would
really be the only recourse. No ability to provide a mask.

Validating dates is pretty tough.

03/06/2005 is a valid date, but is it the one you want. Perhaps use a
calendar control for selecting dates.

 

[martinely95]

As I didn't find and input mask for dates on the Internet, I created this code:

Dim NewString, MyString, mask As String
Dim position As Variant

Private Sub TextBox1_Change()
If IsNumeric(Right(TextBox1.Text, 2)) And Len(TextBox1.Text) >= 11 Then
TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
Else
position = TextBox1.SelStart
MyString = TextBox1.Text
pos = InStr(1, MyString, "_")
If pos > 0 Then
NewString = Left(MyString, pos - 1)
Else
NewString = MyString
End If
If Len(NewString) < 11 Then
    TextBox1.Text = NewString & Right(mask, Len(mask) - Len(NewString))
    TextBox1.SelStart = Len(NewString)
End If
End If
If Len(TextBox1.Text) >= 11 Then
    TextBox1.Text = Left(TextBox1.Text, 10)
End If
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
position = TextBox1.SelStart
If KeyCode = 8 Then
    TextBox1.Text = mask
End If
End Sub

Private Sub UserForm_Initialize()
TextBox1.SelStart = 0
mask = "__.__.____"
TextBox1.Text = mask
End Sub

You have to put this on a Userform. Change the "TextBox1.Text" with the name of your textbox. I hope this is what you need.