vba Userform Input Error Checking

Question

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.....

Instead this line If A + B + C + D + E + F + G + H = 0 Then you could direct use TextBox values: If TextBox1.Text & TextBox2.Text & ... & TextBox8.Text = "" Then — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 2, 2014 at 7:59

GSerg · Accepted Answer · 2014-03-02 14:22:10Z

1

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

answered Mar 2, 2014 at 14:22

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

Comments

Ioannis · Accepted Answer · 2014-03-02 10:07:43Z

0

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!

answered Mar 2, 2014 at 10:07

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

vba Userform Input Error Checking

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related