Code to Loop Through the COntrols of Userform and Prompt if any field is empty

Question

I have a userform whose image is attached below.

What i need is when i press the submit button, it should promptv if there is any textbox left empty in the userform.

Here is my code:

Private Sub CommandButton1_Click()

Dim intTextBox As Integer
For intTextBox = 1 To 2

If Controls("TextBox" & intTextBox) = "" Then
MsgBox ("TextBox" & intTextBox & "blank. Please enter relevant data!")
Exit For
End If
Next intTextBox

End Sub

I am getting error at If Controls("TextBox" & intTextBox) = "" Then as could not find the specified object.

Kindly review and advise.

check the code in my answer below, and see if it works like you wanted — Shai Rado
– Shai Rado, Commented Nov 26, 2016 at 7:47

Shai Rado · Accepted Answer · 2016-11-26 08:10:24Z

1

In order to find which TextBox is left empty, you need to loop through all Controls in your user_Form. Then, for each Control you need to check if it's type TextBox >> if it is, then check if it's empty.

Code

Option Explicit

Private Sub CommandButton1_Click()

Dim ctrl As Control

' loop through all controls in User_Form
For Each ctrl In Me.Controls
    ' check if "TextBox"
    If TypeName(ctrl) = "TextBox" Then

        ' if current TextBox is empty
        If ctrl.Text = "" Then
            MsgBox ctrl.Name & "blank. Please enter relevant data!"
            Exit For
        End If

    End If

    ' check if "ComboBox"
    If TypeName(ctrl) = "ComboBox" Then

        ' if current ComboBox is empty
        If ctrl.Value = "" Then
            MsgBox ctrl.Name & "blank. Please enter relevant data!"
            Exit For
        End If

    End If
Next ctrl

End Sub

edited Nov 26, 2016 at 8:10

answered Nov 26, 2016 at 7:44

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Salman Khan Over a year ago

Thanks Shai. One more question if i want this loop to include the combobox in the userform and then prompt that this combobox box and textbox is empty. how is that possible...

Shai Rado Over a year ago

@SalmanKhan just add anothe If to check if "ComboBox" , OK ?

Salman Khan Over a year ago

@Rado Perfect. Thanks Rado

Salman Khan Over a year ago

@Rado If i want to loop all the controls and if there is only a sinlgle field is empty then it prompt"Please fill in all details" else <code>... how it will be done...

user6432984 Over a year ago

TextBoxes have a Value property that you can use to combine both statements. When I was test I found that using TextBox.Text without the TextBox having focus will throw Error 2185 You can't reference a property or method for a control unless the control has the focus..

Collectives™ on Stack Overflow

Code to Loop Through the COntrols of Userform and Prompt if any field is empty

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related