Check If Each Input Box is filled

Many times you don’t want a user to proceed until all input boxes are filled. To solve this you can loop through each control with a For Each statement. For Each will be especially useful when you there are many controls and you don’t want to reference each name individually. The examples below show how to access the controls when they are either in a frame, in a multi page, or directly on the userform.

Sample UserForm showing ComboBoxes, TextBoxes, Multipages, and frames
Dim fTextBox As Object
Dim xEptTxtName As String

For Each fTextBox In UserFormProjectData.Frame3.Controls
    If TypeName(fTextBox) = "TextBox" Or TypeName(fTextBox) = "ComboBox" Then
        If fTextBox.Text = "" And fTextBox.Enabled <> False Then
            fTextBox.BackColor = vbYellow
            xEptTxtName = xEptTxtName & fTextBox.Name & " is empty" & vbNewLine
        Else
            fTextBox.BackColor = vbWhite
        End If
    End If
Next

    If xEptTxtName <> "" Then
        MsgBox "yellow highlights must be filled out"   'xEptTxtName & vbNewLine & xTxtName"
        Exit Sub
    End If

Below are other ways to access the controls. Use ‘Me’ when the code is written in the userform or use the name of the userform when the code is called outside the userform such as a module.

For Each fTextBox In Me.MultiPage1.Pages("Page3legs").Controls

For Each fTextBox In Me.MultiPage1.Pages(1).Controls

For Each fTextBox In Me.Controls

For Each fTextBox In UserFormProjectData.Controls

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s