Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-18-2019, 06:30 PM
ScottyBee ScottyBee is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Novice
Validate textbox fields in VBA Form before Submit
 
Join Date: Mar 2019
Location: Portland, Oregon
Posts: 12
ScottyBee is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-18-2019, 06:51 PM
Guessed's Avatar
Guessed Guessed is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 03-19-2019, 05:43 PM
ScottyBee ScottyBee is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Novice
Validate textbox fields in VBA Form before Submit
 
Join Date: Mar 2019
Location: Portland, Oregon
Posts: 12
ScottyBee is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 03-19-2019, 06:32 PM
Guessed's Avatar
Guessed Guessed is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #5  
Old 03-20-2019, 06:20 AM
gmaxey gmaxey is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #6  
Old 03-20-2019, 04:46 PM
ScottyBee ScottyBee is offline Validate textbox fields in VBA Form before Submit Windows 10 Validate textbox fields in VBA Form before Submit Office 2016
Novice
Validate textbox fields in VBA Form before Submit
 
Join Date: Mar 2019
Location: Portland, Oregon
Posts: 12
ScottyBee is on a distinguished road
Default 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.
Reply With Quote
Reply

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
Validate textbox fields in VBA Form before Submit VBA Code for a SUBMIT button in a Word form BlueMax Word VBA 3 08-07-2013 06:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft