#1
|
|||
|
|||
Validate textbox fields in VBA Form before Submit
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 |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Purpose
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 |
#4
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Great Ideas that I will put in place
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.
|
Thread Tools | |
Display Modes | |
|
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 |