CODEWITHSUNDEEP
vbaexcel-2013
Zack E
Zack E

Reputation: 706

Loop through TextBoxes to confirm all information has been entered

I have looked through numerous loop posts on here and unfortunately, I have been unable to modify what I have found to serve my needs, so I am asking for some help.

Please note I am still relatively new to loops as you can see by my other posts.

Long and short of my problem is I need to run a check on TextBox controls with a Tag value of "Required" in three different Frames that are all on the same UserForm to make sure they are completed when the user tries to enter a value in a TextBox on that same UserForm. If any of the TextBoxes with the Tag of "Required" have no value then I need a debug print to show me the names of those TextBoxes. From there I will be able to figure out how to add the print output to a msgbox. Below is the code I have started, but I am stuck on how to store each of the TextBoxes that have no value in a variable that I can use for the debug print.

Private Sub yLPLendComp_Enter()
    Dim ctrl As Control
    Dim i As Integer

'   SET THIS TO STORE THE CTRL.NAME IF BLANK??
    i = 0

'   THIS LOOKS THROUGH EACH REQUIRED TEXTBOX (SET BY TAG VALUE)
    For Each ctrl In LP.LoanInfo.Controls
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Tag = "Required" And ctrl.Value = "" Then
                i = i + 1
                Debug.Print ctrl.Name
        End If
    Next ctrl
'   IF THE VALUES ARE BLANK THEN MSG BOX APPEARS AND PRINTS THE EMPTY TEXT BOX NAMES
'    If i = 1 Then
'        Debug.Print ctrl.Name
'        MsgBox "Not all fields that need to be completed are complete please complete your required fields.", vbCritical, UCase("error")
'    End If
End Sub

Upvotes: 0

Views: 272

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Something like this:

Private Sub yLPLendComp_Enter()

    Dim ctrl As Control
    Dim msg As String, sep

    For Each ctrl In LP.LoanInfo.Controls
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Tag = "Required" And ctrl.Value = "" Then
                msg = msg & sep & ctrl.Name
                sep = vbLf
            End if
        End If
    Next ctrl

    If Len(msg) > 0 Then
        MsgBox "One or more required fields need to be completed" & vbLf & msg, _
                 vbCritical, "Missing Information"
    End If
End Sub

If your control names aren't user-friendly, you could use Tag values like "Required:User Friendly Name Here" then check for

If ctrl.Tag Like "Required:*"

and then

msg = msg & sep & Replace(ctrl.Tag, "Required:","")

EDIT: to use text from your linked labels

msg = msg & sep & Me.Controls("Label_" & ctrl.Name).Caption

Upvotes: 1

Related Questions