Disable email function of command button if all form fields are not completed

[derajlance]

Now that I have my forms completed and emails sending the way I need them to send, I want to tweak a few things.

Is there any way to disable the email function of a command button if all form fields are not completed? If not, is there a way to prevent moving to the next field until the one before it is filled out?

I have figured out how to display an error upon exiting the form field with the following code:

Sub ExitText1()
    With ActiveDocument.FormFields("Text1")
        If Len(.Result) < 1 Then
            Application.OnTime When:=Now + TimeValue("00:00:001"), Name:="GoBacktoText1"
            MsgBox "This is a required field."
        End If
    End With
End Sub

But this does not prevent the user from submitting the form and sending the email.



Thanks in advance!

[gmayor]

There is more on validating form fields at http://www.gmayor.com/formfieldmacros.htm however you cannot force users to run the macros.

As you are using macros anyway then it might make more sense to provide a userform to collect the data, write it to the document and to submit the form by e-mail. That way you can more easily check whether the required fields have been filled.

The alternative is to add code to the submit button to validate each of the required fields before allowing the process to complete.

[derajlance]

Originally Posted by gmayor

The alternative is to add code to the submit button to validate each of the required fields before allowing the process to complete.

I think this is what I am trying to accomplish. My goal is that when they click "submit", it validates the form fields and if any of them are blank, the submit process will not complete and will display a message stating, "Field _ cannot be blank. Please complete the form and try again."

I have found several solutions to giving pop-up reminders, but nothing to terminate the process when the reminder shows.

[derajlance]

I think another option would be something like this:

    With ActiveDocument.FormFields("TextBox1")
        If Len(.Result) < 1 Then CommandButton1.Enabled = False

Not sure if that would work and if so, where it would need to go.

[gmayor]

Presumably this relates to your other thread, in which case, immediately after the DIM statements at the top of the macro, enter or copy the following.

Dim oFF As FormField

    For Each oFF In ActiveDocument.FormFields
        If oFF.Result = "" Then
            oFF.Select
            MsgBox "Field cannot be left empty"
            GoTo lbl_Exit
        End If
    Next oFF

[derajlance]

Thanks again Graham. I will test this tomorrow and will update the thread. I truly appreciate your assistance!

[derajlance]

That worked!

One last question, simply because I thought I had found the solution on your website, but it doesn't seem to be working.

To prevent them from sending duplicates, I am trying to clear the text fields once the email from the command button is sent. I found your code for resetting form fields, but it doesn't seem to be resetting them.

I also tried to just add the following after the email code, but nothing happens:

Text1 = ""
Text 2 = ""

[gmayor]

The following will reset the fields

Dim oFF As FormField
    For Each oFF In ActiveDocument.FormFields
        If oFF.Type = wdFieldFormTextInput Then
            oFF.TextInput.Clear
        End If
    Next oFF
    Set oFF = Nothing

You don't need the DIM statement if you are adding it to the end of the original macro as it is already defined.

[derajlance]

Alright, I have my userform done and it is sending properly, but the code above to clear afterwards isn't working. It doesn't seem to be doing anything at all.

I am not sure if it is because I have the application set to not visible or what.
I am going to post my full code below in hopes that it may help someone to help me identify the issue. I want to either have the form clear and ready to be filled out and submitted again after the email is sent OR I want to completely exit the document so they have to open it again and start from scratch.

This document:

Sub AutoNew()
Application.Visible = False
UserForm1.Show
End Sub

UserForm1:

Private Sub UserForm1_Initialize()
Me.Caption = "Escalation"
Me.Label1.Caption = "SVM #:"
Me.Label2.Caption = "Claim #:"
Me.Label3.Caption = "Customer Name:"
Me.Label4.Caption = "Provider # and/or name:"
Me.Label5.Caption = "Reason for escalation:"
Me.CommandButton1.Caption = "Submit"
End Sub


Private Sub UserForm_Terminate()
Application.Quit SaveChanges:=False
End Sub

Private Sub CommandButton1_Click()

    Set SVM = ActiveDocument.Bookmarks("SVM").Range
    SVM.Text = Me.TextBox1.Value
    
    Set Claim = ActiveDocument.Bookmarks("Claim").Range
    Claim.Text = Me.TextBox2.Value
    
    Set Customer = ActiveDocument.Bookmarks("Customer").Range
    Customer.Text = Me.TextBox3.Value
    
    Set Provider = ActiveDocument.Bookmarks("Provider").Range
    Provider.Text = Me.TextBox4.Value
    
    Set Reason = ActiveDocument.Bookmarks("Reason").Range
    Reason.Text = Me.TextBox5.Value
    

Static running As Boolean
If running Then Exit Sub
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Or TextBox4 = "" Or TextBox5 = "" Then MsgBox "All fields are required. Complete the form before clicking submit.": Exit Sub
running = True

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objInspector As Object
Dim objDoc As Word.Document
Dim objRange As Range

    ActiveDocument.Content.Copy
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        MsgBox "Outlook is not running."
        GoTo lbl_Exit
    End If
    On Error GoTo 0

    Set objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
        .to = "email"
        .cc = "email"
        .Subject = "Escalation"
        Set objInspector = .GetInspector
        Set objDoc = objInspector.WordEditor
        Set objRange = objDoc.Range(0, 0)
        .Display
        objRange.Paste
        .send
    End With
lbl_Exit:
    Set objDoc = Nothing
    Set objRange = Nothing
    Set objOutlookMsg = Nothing
    Set objInspector = Nothing
    Set objOutlook = Nothing
    Exit Sub
End Sub

[gmayor]

Please see the attached example, which you can modify as required. You can run the macro as many times as you wish and the document will be updated to reflect the form contents. The form is unloaded each time so does not retain the values. Create a new document from the template.

[derajlance]

It took a while, but finally got this taken care of. Thank you again!