Microsoft Office Forums - View Single Post - Validate textbox fields in VBA Form before Submit

gmaxey · 03-20-2019, 06:20 AM

As an alternative to after the fact validation, you can disable your command execute until all required conditions are met. Adapting Andrew's code, here textboxes 1 - 5 simply require input. Textbox 6 requires a numeric input. Textbox7 a date input and finally Textbox 8 a formatted SSN input:

Code:

Option Explicit
Private Sub TextBox1_Change()
  Validate
End Sub
Private Sub TextBox2_Change()
  Validate
End Sub

Private Sub TextBox3_Change()
  Validate
End Sub

Private Sub TextBox4_Change()
  Validate
End Sub

Private Sub TextBox5_Change()
  Validate
End Sub

Private Sub TextBox6_Change()
  Validate
End Sub

Private Sub TextBox7_Change()
  Validate
End Sub
Private Sub TextBox8_Change()
  Validate
End Sub

Private Sub UserForm_Initialize()
    CommandButton1.Enabled = False
End Sub


Sub Validate()
Dim lngIndex As Long
  CommandButton1.Enabled = True
  For lngIndex = 1 To 8
    Select Case lngIndex
      Case 1, 2, 3, 4, 5
        'Text only required
        If Controls("TextBox" & lngIndex).Text = vbNullString Then
          CommandButton1.Enabled = False
        End If
      Case 6
        'Numeric required
        If Not IsNumeric(Controls("TextBox" & lngIndex).Text) Then
          CommandButton1.Enabled = False
        End If
      Case 7
        'Date required
        If Not IsDate(Controls("TextBox" & lngIndex).Text) Then
          CommandButton1.Enabled = False
        End If
      Case 8
        'A formatted SSN is required
        If Not Controls("TextBox" & lngIndex).Text Like "###-##-####" Then
          CommandButton1.Enabled = False
        End If
    End Select
  Next lngIndex
End Sub