Data validation for userform textboxes

[Von Pookie]

I have a userform with 10+ textboxes in which the user will enter information to be entered into the spreadsheet when clicking a button.

There are 2 boxes where I would like to limit what can be entered in them.

I can figure it out for cells, but can't seem to get it to work for textboxes.

For one box, they will be entering zip codes. I would like to limit this to only numbers, and 5 characters in the box.

The other box is a little trickier. It's for a phone number. By searching the board, I found some code that changes the number entered into (###) ###-#### format. However, this only works if a whole number is entered (ex. 1234567890). I've tried getting it to also format this way if a number is entered as 123-456-7890 or 123/456/7890, but can't seem to get it. I would also like to be able to limit the number of characters in this box to either 10 or 12 (depending on if I can get it to work around having to enter as a 10-digit whole number).

If anyone could shed some light onto this, t'would be quite appreciated.

Thankees, and good night

 

[Ivan F Moala]

Thanks Jack.....I was a bit iffy about that
But......

 

[Legacy 98055]

Jack, Ivan.
I packaged a tiny VB app containing the control in a setup file, ran it on my other machine which has Office 97, and the masked edit was then available to VBA as an additional control? I don't see how this would be illegal??? The only problem is the help file did not work when the control was selected and F1 pressed. The help file does work seperately, however.
As far as I can tell, the standard controls which came with Vis Studio can be distributed royalty free.
Nonetheless, I'm not going to lose any sleep over it.
Tom

P.S. Kristy, to keep the email small, I did not include the VB runtime files in your setup package. If the control does not work with 5.0, if that is what you are running, then you can download what you need at CNET or a million other places... Here is CNET's link: http://download.com.com/3000-2070-10023820.html?legacy=cnet

Tom
This message was edited by TsTom on 2002-06-02 06:42

 

[Ivan F Moala]

Thats great Tom.......I'm sure Kristy will appreciate your efforts....

cheers

 

[Jack in the UK]

Hi Guys no stress, I just had a message to say.. If you get me check PM ill explain more

 

[robfo0]

Von Pookie,
This seems to be a similar problem i had before. Try this link:
http://www.mrexcel.com/board/viewtopic.php?topic=3886&forum=2

and also, you could try doing this:

Private Sub textbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
textbox1 = Format(Replace(textbox1,"-",""), "(###)###-####")
End Sub

If you use this for your textbox named "textbox1" (change name accordingly), it will replace any "-" with a blank, then format the number correctly. You could also put in another Replace around that for "/", but it will only handle those, not all foreign characters.

Also, you can easily limit the number of characters input in the design of your userform. In the properties, there is a value for "MaxLength", just enter the max you want it to be there and it will limit it.

Hope that helps
This message was edited by robfo0 on 2002-06-02 12:21

 

[Von Pookie]

Boy, you guys have been busy on my little "problem."

At any rate, Tom, I got the file, but I do have several questions... 1) do I have to install it in a certain directory? 2) do you know if it will work with Excel 2002/XP?

I found out that the person I'm making it for is using the XP version, so I've got to work on it down here in the basement

Even if that won't work, the option that robfo0 works. I tested it by entering a number as 123-456-7890 and 123/456/7890 and it does get it formatted to what I want it as.

Potential "crisis" averted for now, it seems. If I come up with anything else I can't seem to get working I'll post away.

Thanks, guys

 

[Legacy 98055]

Hi Kristy.
If your making the book for another user then it might be too much trouble to bother installing the control. I'm curious. I think the Masked edit control is standard with Office 2000 and up???
Anyone know? I can't tell because of other software I have installed on my system.
Kristy, next time your in your VBE working on your userform, right-click on your 'Control Toolbox', choose additional controls, and then scroll down and search for 'Microsoft Masked Edit'.
Let me know. I think you may already have it.
Thanks,
Tom

 

[Von Pookie]

Hi Tom,

It doesn't seem to be in here. The list under additional controls goes from Microsoft Listview Control to Microsoft Netshow player. No Masked Edit anywhere.

_________________<font color=red>~*</font><font color=blue>Kristy</font><font color=red>*~</font>

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."

</img>
This message was edited by Von Pookie on 2002-06-02 20:10

 

[Legacy 98055]

OK.
To answer your other question, no.
You do not need to install in any specific directory. The program is actually a VB standalone. A form with a masked edit drawn on. This would necessitate the setup package to include and install the control on your system. Play around with it. It's a great control to have. If you find that you do want to use it on other machines, it's not a huge deal to install it manually. Just two small files and typing a command on your run dialog from Start, Run, to register the file. Let me know if you want to bother or not.
Tom

If Necc. Please post another thread. Cleaning out my favorites
This message was edited by TsTom on 2002-06-03 05:59