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