Rather than looping manually through every TextBox on the Form, I came up with the following: A function with the Form as a single argument. Loop through each control on the Form, (using “For Each Control etc”) and if it’s a TextBox which is empty, it returns Err.Number = 0. If this is true, I change the BackColor of the offending TextBox to light pink, and the function returns True. (If the TextBox is not empty, and it’s BackColor is light pink, I return the BackColor to white. One can store the original color and restore it.) Then call Err. Clear, and continue in the loop. All TextBoxes which are empty will show up as light pink.
To test, create a Form with several TextBoxes on it; their names are immaterial, and they can even be in some container, like a Frame. Create a Command Button, named TestEmpty. Following is the code:
Private Sub TestEmpty_Click()
If IsEmpty(Me) Then
MsgBox "Some textboxes are still empty"
Function IsEmpty(Frm As Form) As Boolean
Dim tmpControl As Control
On Error Resume Next
IsEmpty = False
For Each tmpControl In Frm.Controls
If Trim(tmpControl.Text) = "" Then
If Err.Number = 0 Then
IsEmpty = True
tmpControl.BackColor = &HFFC0FF 'light pink
If tmpControl.BackColor = &HFFC0FF Then
tmpControl.BackColor = QBColor(15) 'White
Run the program. Fill in some of the TextBoxes, and click on the Command Button, You will see the empty TextBoxes BackColor change to light pink. If you fill these in and click again, their BackColor will change to white. Note: If the procedure is in a Module, then the argument is the Form name.
Tip Submitted By: Yehuda Hilewitz