Validation data entry in multiple fields in userform

[ijswalker]

Hi

I have a userform and want to be able to restrict what kind of data is entered into a some fields.

I am able to validate one text box but I would like to validate more than one textbox in my user form without having to write 30+ pieces of code

This is the code I have for validating data entry in a single text box called UNIT1.

Code:Private Sub UNIT1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(Me.UNIT1.Value) Then
Me.UNIT1.Value = vbNullString
Call MsgBox("You may only enter numbers here", vbCritical, "Text entry")
End If
End Sub


Can anyone help?


Thanks

Ian

[XLGibbs]

You could write a couple of routines that can be called by each click event....

Such as the above sample for numeric validation, perhaps another for text...

You can then call it..


Public blnDataOK as Boolean

Private Sub UNIT1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ValidateNumbers Unit1.Value
If blnDataOK = False then Me.Unit1.Value = vbNullString
End Sub

Sub ValidateNumbers(byVal x as string)

If Not IsNumeric(x) Then
blnDataOK = False
MsgBox("You may only enter numbers here", vbCritical, "Verify entry")
End If
End Sub



Where the public declaration occurs in the Forms module

You would want to be sure to set the blnDataOK flag to TRUE by default, and only set it to false via the subroutine...

that is one alternative...

otherwise, you would want to build a class to handle all form events, regardless of control....and building that it this point would mean more work than writing 30 snippets of code.

[ijswalker]

The 30 pieces of code was what I really was wanting to know. I suppose copying and pasting 30 times sounds the easier option,

Thanks

Ian

[XLGibbs]

Yeah, if you had thought of this on the front end it would have been easier...the only other option is if all the data validation were identical (ISNumeric)

You might be able to set up a for each loop through the controls....

[Bob Phillips]

Setting up an event sink class module is really trivial, not at all hard, 5 mins work. The main problem is that application events to not support all events, such bas Enter or Exit. IF you had some other event, such as Change, it would work easily.

[ijswalker]

I am a bit of a novice at this. How would I set up a change event using the code I first posted to make it look at whatever cell I was entering info into?

Thanks

Ian

[XLGibbs]

On the worksheet?

You would use the Worksheet_Change event


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A10")) Then

With Target
If Not IsNumeric(.Value) Then
MsgBox "You can only enter numbers here"
.Value = ""
.Activate
End If
End With
End If

End Sub


You specify the cells you want to validate in the Range( ) argument

[ijswalker]

No not on a worksheet but in the form. I meant to write textbox and not cell. Sorry.


Thanks

Ian

[XLGibbs]

You can loop through them at the end for validation....using something like this which will cycle through the controls on your form, and if it is a textbox, then evaluate if it is a number, and return the name of the textbox in the msgbox. You can obviously put whatever actions you like in there...

Set the Sub to fire on a button click. Unless you want to code each Text box_Change event to handle it...


Sub ValidateEntry()

Dim ctTB As Control
For Each ctTB In UserForm1.Controls
If TypeName(ctTB) = "TextBox" Then

If Not IsNumeric(ctTB.Text) Then
MsgBox "Must enter a number in " & ctTB.Name
End If
End If
Next ctTB

End Sub

[Norie]

See the attached for an example using a class module.

[ijswalker]

Hi Norie,


That looks like what I want to do. I have quite a few textboxes in my form. Some are values and some are text.

How would I isolate the text boxes that I want in the class module as opposed to the ones I don't?

Thanks

Ian

[Bob Phillips]

That looks like what I want to do. I have quite a few textboxes in my form. Some are values and some are text.

How would I isolate the text boxes that I want in the class module as opposed to the ones I don't?

You could set the Tag property of the required textboxes, and test that when adding the textboxes to the group.

[Norie]

Ian

There are various ways to do that.

xld has mentioned one.

Another is to test the name of the control.

If you have followed any sort of naming convention you could use that test to decide which textboxes to add and which not to.

Also within the class module you could again test the control's Tag/name to decide what action to actually take.

[ijswalker]

Ok Norie,

I'll go into unchartered waters and give that a go. You guys have helped so much in the last few months and I have been learning a huge amount with all of this good stuff.

Thanks

Ian

[tomrock]

Can you enter negative numbers with this class method? When I type a hyphen I get the "Only numeric data, thank you" message.

[Norie]

Not with the way it's currently coded.

It could be added/changed to allow negative numbers.

I'll see if I can come up with something and post back.

Inserting this before the existing If in the class module appears to work.

If Len(TextGroup.Value) = 1 And TextGroup.Value = "-" Then Exit Sub

[tomrock]

Thanks for posting this fix -- Validation seems to be something I need for every form but I haven't really found much discussion of it, just random bits here and there.