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]

On 2002-05-31 21:43, Von Pookie wrote:
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

Hi Kristy
I thought you may need a validation code when I looked @ your code before....this is typical when setting up a prgm to have some sought of validation....

Here is some code to set the validation for
ph. # using ONLY Numbers AND "-"

you can change it as required to suit and also change just to have numbers.
To limit the len just set the Textbox's MaxLenght property to 12 or what ever.

<pre/>
Private Sub Textbox1_Change()
'// Allow ONLY Numbers AND -
'// TextBox number mask
Dim Curpos As Double
Curpos = TextBox1.SelStart
If Curpos = 1 Then
'// need to handle -- in a row
If Not ValidateNumeric(Right(TextBox1.Text, 1)) Then
TextBox1.Text = Left(TextBox1.Text, Curpos - 1)
End If
Else
'//Handle backspace past textlen
On Error Resume Next
If Not ValidateNumeric(Right(TextBox1.Text, 1), Mid(TextBox1.Text, Curpos - 1, 1)) Then
TextBox1.Text = Left(TextBox1.Text, Curpos - 1)
End If
Err.Clear
End If
End Sub

Private Function ValidateNumeric(strText As String, Optional strPrev As String) As Boolean
ValidateNumeric = CBool(strText = "" Or IsNumeric(strText) _
Or strText = "-" And strPrev <> "-")
End Function
</pre>

 

[Von Pookie]

Thanks for that Ivan.

It doesn't quite do what I'm looking for, though.

As it is right now, I've limited it to 10 characters, since if you enter a number as 1234567890, when I tab to the next box it will automatically format it to (123) 456-7890.

If I change the limit to 12 characters, I can enter either 123-456-7890 or 123/456/7890, but it won't change the format to (123) 456-7890.

I guess that for the time being, I will just leave it as 10 characters only, and just stick a label next to it stating to enter the phone # as a whole number.

I hope that makes sense...

_________________<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-01 14:43

 

[Legacy 98055]

See if the masked edit control is available on your system.
Tom

 

[Von Pookie]

I can't seem to find anything on that in the help file. Also, the fact that I've never heard of it doesn't help this fact either..

 

[Ivan F Moala]

I believe that control is avail but only if you have VB installed...ie. Visual Basics Application and NOT VBA.
It's something that should really be a part of VBA, so coders in VBA don't have to mess around trying to mask for diff inputs, somethig you realise you have to do when developing for other users ie. Limit the users WRONG inputs.

 

[Legacy 98055]

Hi Ivan
Would it be illegal for someone to email Von Pookie the control?
I was not sure which application on my sys installed the control. I do have Vis Studio, so I guess that is where it came from. I'm thinking that it would not be illegal??? If I created an app for someone which utilized this control, would it not be available to them for use in their own projects?
Thanks,
Tom

 

[Ivan F Moala]

On 2002-06-02 05:02, TsTom wrote:
Hi Ivan
Would it be illegal for someone to email Von Pookie the control?
I was not sure which application on my sys installed the control. I do have Vis Studio, so I guess that is where it came from. I'm thinking that it would not be illegal??? If I created an app for someone which utilized this control, would it not be available to them for use in their own projects?
Thanks,
Tom

I guess so ??
She may have to register it via Regserve32
if she can't get it to reference without an error ??
ie.
Select Start > Run and type "RegSvr32
"C:thepathtothefile??.dll" without the quotes.

If the machine does not have RegSvr32.exe in its WindowsSystem folder,

You can download it from here:

http://support.microsoft.com/download/support/mslfiles/Regsv32a.exe

 

[Legacy 98055]

I'll give it a shot Ivan.
Hi Von Pookie.
The masked edit control is a lot more functional than a regular text box control.
I'll email you a small setup program which will install the control. See the attached help files as well.
You can then mask your phone numbers, addresses, zipcodes, ect.
Tom

 

[Jack in the UK]

I know Microsoft have such tight laws and controls over Pirate kit.

I would say anything bar Excel or txt docs would be class doggy in Microsoft’s eye.

SIMPLE if a system does not have X, why not? Version or types of install are possible, and much is IT Mangers fault, with slim installs.

IF you send a control from says Visual.NET it might not work, and really is not catered for by the other end.

With what you know of, me and my background this post might sound rich, but I just do not want the possibility the extra can / could cause hassles later.

Check with the TI Dept first and then move on, then again, most IT Dept are not Excel-lers!

Also remember what you might have others might not!