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.
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
Coding with Visual Basic 