CODEWITHSUNDEEP
excelvba
user3369800
user3369800

Reputation: 1

vba Userform Input Error Checking

I have a userform in Excel 2010. There are 8 text boxes. They do not all need to be filled in to submit data. If all 8 text boxes are blank, a msgbox appears. Below is my code for doing just that. Is there a more elegant way of coding this? Thank you. 

Dim A
Dim B
Dim C
Dim D
Dim E
Dim F
Dim G
Dim H

If TextBox1.Text = "" Then
    A = 0
Else
    A = 1
End If

If TextBox2.Text = "" Then
    B = 0
Else
    B = 1
End If

If TextBox3.Text = "" Then
    C = 0
Else
    C = 1
End If

If TextBox4.Text = "" Then
    D = 0
Else
    D = 1
End If

If TextBox5.Text = "" Then
    E = 0
Else
    E = 1
End If

If TextBox6.Text = "" Then
    F = 0
Else
    F = 1
End If

If TextBox7.Text = "" Then
    G = 0
Else
    G = 1
End If

If TextBox8.Text = "" Then
    H = 0
Else
    H = 1
End If

If A + B + C + D + E + F + G + H = 0 Then
    MsgBox "       Enter Some Data,", vbOKOnly + vbCritical, "ERROR!"

Else

'.....rest of code.....

Upvotes: 0

Views: 2115

Answers (2)

GSerg
GSerg

Reputation: 78155

Private Sub CommandButton1_Click()
  If Not HasSomeInput() Then MsgBox "Enter Some Data,", vbOKOnly Or vbCritical, "ERROR!"
End Sub

Private Function HasSomeInput() As Boolean
  Dim c As MSForms.Control
  Dim t As MSForms.TextBox

  For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
      Set t = c
      If Len(t.Text) > 0 Then
        HasSomeInput = True
        Exit Function
      End If
    End If
  Next
End Function

Upvotes: 1

Ioannis
Ioannis

Reputation: 5388

I would do something like

    Dim cCont As Control
    Dim sEntries As String
    ' Change UserForm1 to the actual name of the userform
    For Each cCont In UserForm1.Controls
        If TypeOf cCont Is msforms.TextBox Then _
          sEntries = sEntries & CStr(cCont)
    Next cCont
    If Trim(sEntries) = vbNullString Then _
      MsgBox vbTab & "Enter Some Data!", vbOKOnly + vbCritical, "ERROR!"

I hope this helps!

Upvotes: 0

Related Questions