Reuse text box validation code for multiple text boxes

[zinzah]

hi again...

I have a userform...with multiple frames...each containing multiple text boxes.
i am using this code to validate one text box in one part of my code

Sub tbxEndGameNum_Change()
Debug.Print "tbxEndGameNum_Change()"


If Len(tbxEndGameNum) = 4 Then
'Application.ScreenUpdating = False

  If IsNumeric(tbxEndGameNum.Value) = False Or tbxEndGameNum.Value = vbNullString Then
        If (tbxEndGameNum.Value < 1300 Or tbxEndGameNum.Value > 2500) Then
            Selection = MsgBox("Please enter a valid 4 digit game number", 1, "INVALID GAME NUMBER!")
            tbxEndGameNum.Value = ""
            tbxEndGameNum.SetFocus
            Exit Sub
        End If
    Else
        MsgBox "Your input data is not valid"
        tbxEndGameNum.Value = ""
        tbxEndGameNum.SetFocus
        Exit Sub

End If

Call Processing
End If
End Sub

Wondering what i have to do/where do i need to move this/how do i change it so that i can call the same validations for all the textboxes when data in entered?

 

[DanteAmor]

Hi @zinzah. Thanks for posting on the forum.

In order for me to better understand the problem, I would like to ask a few questions:

1. Is this validation correct?

  If IsNumeric(tbxEndGameNum.Value) = False Or tbxEndGameNum.Value = vbNullString Then
        If (tbxEndGameNum.Value < 1300 Or tbxEndGameNum.Value > 2500) Then

Because if I put a number, for example: "20002, the first validation rejects it. And if I put 4 letters: "abcd" the second validation rejects it, so I can't get to the Processing procedure.​

​

I think it should be TRUE, so if I put 2000 it's accepted by the first validation, it's also accepted by the second validation because it's between 1300 and 2500, then I can get to the Processing procedure.​

Could you confirm my question?​

2. Are ALL the textboxes of your userform that must be validated or only some specific ones (how many)?

3. Processing. Is this procedure in the userfor or in a module?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[zinzah]

Thanks for the reply....
1) Yes. it should be TRUE. i changed it to FALSE for testing and did not change it back when i pasted the code.
2) all textboxes should be validated that way. At this point, there are 16...that number will grow
3) it is a Sub in the Userform. All text boxes to be validated are on the Userform

 

[DanteAmor]

Thanks for answering.

Perform the following steps:

1. Create a class module:​

In the VBA window from the Insert menu, select the Class Module, as shown below.​

​

2. Check that the class name is Class1.​

​

3. Put the following code in the class module:​

​

Public WithEvents MultTextbox As MSForms.TextBox

Private Sub MultTextbox_Change()
  With MultTextbox
    If Len(.Value) = 4 Then
      If IsNumeric(.Value) = True Or .Value = vbNullString Then
        If (.Value < 1300 Or .Value > 2500) Then
          Selection = MsgBox("Please enter a valid 4 digit game number", 1, "INVALID GAME NUMBER!")
          .Value = ""
          .SetFocus
          Exit Sub
        End If
      Else
        MsgBox "Your input data is not valid"
        .Value = ""
        .SetFocus
        Exit Sub
      End If
   
      Call UserForm1.Processing
    End If
  End With
End Sub

4. Update UserForm1 with the name of your userform on this line:​

Call UserForm1.Processing

5. In your form code put the following:​

Dim TxtBx() As New Class1         'At the start of all code

Private Sub UserForm_Initialize()
  Dim i As Long, n As Long, ctrl As MSForms.Control
  i = 1
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
      ReDim Preserve TxtBx(i)
      Set TxtBx(i).MultTextbox = ctrl
      i = i + 1
    End If
  Next
End Sub

Done, run your form and try any textbox.​

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------​

 

[zinzah]

@DanteAmor

Excellent! Work great! Thank you...Need to learn more about Classes...

two questions....

1) if i want to exclude a text box from the validation...add this?

Private Sub MultTextbox_Change()
  With MultTextbox

[COLOR=rgb(184, 49, 47)]if .name = tbxDontValidate then exit sub[/COLOR]

    If Len(.Value) = 4 Then

and the .SetFocus command doesn't seem to be working? I have to click back into each text box if it throws the error....

 

[DanteAmor]

1) if i want to exclude a text box from the validation...add this?

The name is in quotes.

I prefer to use the Case statement. But it's better to do it in the Initialize event. This way it is easier to exclude multiple names:

Private Sub UserForm_Initialize()
  Dim i As Long, n As Long, ctrl As MSForms.Control
  i = 1
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
      Select Case ctrl.Name
        Case "tbxDontValidate", "tbxWithoutValidate"
        
        Case Else
          ReDim Preserve TxtBx(i)
          Set TxtBx(i).MultTextbox = ctrl
          i = i + 1
      End Select
    End If
  Next
End Sub

----------------------------------------------------

and the .SetFocus command doesn't seem to be working?

It works for me, if I write for example 1234 when I get to 4, the message appears, I press Ok, the textbox is cleared and the cursor returns to the textbox.

Can you explain what data you write exactly and what should happen?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[zinzah]

if i enter a valid value, no issues and processing continues
If i enter a bad value, 1000 or 3000, i get the MsgBox("Please enter a valid 4 digit game number", 1, "INVALID GAME NUMBER!"), click OK, the text box is cleared, but the focus does not return to the textbox
If i enter 'abcd', i get the MsgBox "Your input data is not valid", click OK, the text box is cleared, but the focus does not return to the textbox

 

[DanteAmor]

If i enter a bad value, 1000 or 3000, i get the MsgBox("Please enter a valid 4 digit game number", 1, "INVALID GAME NUMBER!"), click OK, the text box is cleared, but the focus does not return to the textbox
If i enter 'abcd', i get the MsgBox "Your input data is not valid", click OK, the text box is cleared, but the focus does not return to the textbox

That is very strange it doesn't happen with my test file.

Do you have more code in your userform?

I share my file for you to see and comment.

class and textbox.xlsm

drive.google.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[zinzah]

There is nothing else going on while entering anything into the textbox
i added some Debug statements and they say that the text box i was in does have focus (did it twice) but if i start typing, nothing...i have to reclick into the textbox

Workbook_Open
MultTextbox_Change
MultTextbox_Change
MultTextbox_Change
MultTextbox_Change
LastTopPrizeNumber()
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum <---------------------
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum_Change()
MultTextbox_Change
tbxEndGameNum <---------------------

Code in LastTopPrizeNumber():

Sub LastTopPrizeNumber()
Debug.Print "LastTopPrizeNumber()"
    lblEnterGameNum.Visible = True
    tbxEndGameNum.Visible = True
    tbxEndGameNum.SetFocus
End Sub

This is triggered by the selection of a certain radio button


Code in tbxEndGameNum_Change()

Sub tbxEndGameNum_Change()
Debug.Print "tbxEndGameNum_Change()"
End Sub

 

[DanteAmor]

Did you try my file?

Can you share your file?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.