Run-time Error 438 When Data Entered in all TextBoxes

[poopiebear]

I keep getting Run-time error 438 and have no idea why. The premise of this code is to check to see if there are any empty textboxes before a new userform appears with the answer. It works when there is at least one textbox that doesn't have data it to display a message box, but when all the textboxes have data in there, the error pops up. What am I missing?

This coding is in UserForm1 and if all the fields are completed, I want UserForm2 to open.

'Validates if data is entered in all fields
Private Sub CommandButton1_Click()
    Dim cCont As Control
       For Each cCont In Me.Controls
        If TypeName(cCont) = "TextBox" And cCont.Value = "" And cCont.Visible <> False Then
         MsgBox "Please Complete All Fields"
         OK = True
         GoTo L1
         Exit For
         End If
        Next cCont
 
'Displays TCB amount in new window
    UserForm2.Show
L1:
End Sub

This is the line that gets highlighted when the error appears:

If TypeName(cCont) = "TextBox" And cCont.Value = "" And cCont.Visible <> False Then

 

[Norie]

Move the check for cCont.Value and cCont.Visible after you've checked the control is a textbox.

Currently VBA will try and evaluate those 2 properties for every control, regardless if it's a textbox or not.

Some controls don't have a Value property, not 100% sure about Visible, so that's why you get the 438 Object doesn't support this property... error.

Something like this perhaps

Option Explicit
 
'Validates if data is entered in all fields
Private Sub CommandButton1_Click()
Dim cCont As Control
 
    For Each cCont In Me.Controls
       
        'check if control is a textbox
        If TypeName(cCont) = "TextBox" Then

           ' now check the 2 properties
            If cCont.Value = "" And cCont.Visible <> False Then
                MsgBox "Please Complete All Fields"
                OK = True
                GoTo L1
            End If
                        
        End If
                
    Next cCont
 
    'Displays TCB amount in new window
    UserForm2.Show
L1:
End Sub

 

[poopiebear]

You are a god! THANK YOU SO MUCH!!! It works beautifully now!