VBA Multiple Textboxes Validation control

Question

Dears,

I am preparing a simple userform to let a teacher to enter each student’s name and height in a class. There are two textboxes (textbox_name and textbox_height) and one “add to record” command button. I tried to make some validation control to prevent empty string to be entered. My validation control is in case textbox is empty, when the click the “add to record” button, the corresponding empty textbox turns pink; and when both textboxes are empty, both textboxes should turn pink, however only the first textbox turns pink in my below codes. Can you tell me why and how to correct it? Thanks.

Private Sub Cmd_addtorecord_Click()

    If TextBox_Name = "" Then
    
        TextBox_Name.BackColor = rgbPink
        Exit Sub
    
    End If
    
    If TextBox_height = "" Then
    
        TextBox_height.BackColor = rgbPink
        Exit Sub
    
    End If

    '....

End sub

You Exit Sub if the first textbox is empty, so you never check the second one when the first is empty — Tim Williams
– Tim Williams, Commented Aug 6, 2021 at 6:33

Tim Williams · Accepted Answer · 2021-08-06 15:41:29Z

1

You can do it like this:

Private Sub Cmd_addtorecord_Click()
    'ensure required fields have content
    If FlagEmpty(Array(TextBox_Name, TextBox_height)) > 0 Then
        MsgBox "One or more required values are missing", vbExclamation
        Exit Sub
    End If
    
    '....

End Sub

'check required textboxes for content, and flag if empty
'  return number of empty textboxes
Function FlagEmpty(arrControls) As Long
    Dim i As Long, numEmpty As Long, clr As Long
    For i = LBound(arrControls) To UBound(arrControls)
        With arrControls(i)
            clr = IIf(Len(Trim(.Value)) > 0, vbWhite, rgbPink)
            .BackColor = clr
            If clr = rngpink Then numEmpty = numEmpty + 1
        End With
    Loop
    FlagEmpty = numEmpty
End Function

answered Aug 6, 2021 at 15:41

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

Yes and I have tried your answer . Thanks very much!

Ben Mega · Accepted Answer · 2021-08-07 03:39:31Z

0

You're Exiting the sub before you get to the second textbox. Change your code to something like this:

Private Sub Cmd_addtorecord_Click()

    If TextBox_Name = ""  or TextBox_height = "" Then
        If TextBox_Name = "" Then
            TextBox_Name.BackColor = rgbPink
        End If
        If TextBox_height = "" Then
            TextBox_height.BackColor = rgbPink
        End If
        Exit Sub
    End If
    


    '....

End sub

answered Aug 7, 2021 at 3:39

Ben Mega

5222 silver badges10 bronze badges

Collectives™ on Stack Overflow

VBA Multiple Textboxes Validation control

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related