Validate textbox fields in VBA Form before Submit

ScottyBee · #1 03-18-2019, 06:30 PM

I have a user form I created in VBA that has 8 textboxes for the user to complete. The controls are named txt1, txt2......txt8.

I want to have VBA verify that all fields have been filled in before the code returns the results to the document.

I have a command button in the form that takes the value of each textbox field and inserts it at the designated bookmark in in the document. I successfully added code to the button click event that will display a message box when the user leaves a field empty.

My problem is that I cannot figure out how to break execution of the code which will automatically return focus to the empty field and STAY there. I do have code in the click event to return focus to the empty field but the code continues to run and inserts a null value in the document.

Any ideas are greatly appreciated. BTW, I have done multiple searches on the internet and in here but could not find an answer.

I am using Word 2016 in Windows 10. Thanks

Guessed · #2 03-18-2019, 06:51 PM

You haven't provided any code so you will need to adapt this to your purposes

Code:

Private Sub btnOK_Click()
  Dim i As Integer, aCtl As Control
  For i = 1 To 8
    With Me.Controls("TextBox" & i)
      If .Value = "" Then
        MsgBox "Nothing in this one"
        .SetFocus
        Exit Sub
      Else
        MsgBox .Name & vbCr & .Value
      End If
    End With
  Next i
End Sub

ScottyBee · #3 03-19-2019, 05:43 PM

Thanks Guess, you helped me make some major progress. I did have one question though. What is the purpose of declaringaCtl as a Control since it is not used in any of the code? Thanks

Dim i As Integer, aCtl As Control

Guessed · #4 03-19-2019, 06:32 PM

In the end I decided against using the aCtl object and didn't remove it - you can delete that declaration.

I initially had plans to iterate all the controls on the form but figured it was easier to use a numbered pattern on the names to just hit specific controls.

gmaxey · #5 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

ScottyBee · #6 03-20-2019, 04:46 PM

Thanks Guessed and GMaxey for all of your help. I will have time tomorrow or Friday to implement your ideas and will keep you updated on my progress. I really appreciate it.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Outlook Custom form - submit and approve buttons	R Mohan	Outlook	0	06-18-2018 12:57 PM
Infopath PDF attached to Form not showing in Email Submit	CliffEstel	Misc	0	09-20-2016 11:04 AM
Submit form	deb	Word	0	06-20-2015 07:01 AM
Macro to keep formatted form fields after mail merge or replace text with formatted form fields	jer85	Word VBA	2	04-05-2015 10:00 PM
VBA Code for a SUBMIT button in a Word form	BlueMax	Word VBA	3	08-07-2013 06:26 AM