Using Excel to Build a Form with Validation on Click

[infinikate]

Hey Y'all

I'm creating a form in Excel that on click sends an email, but I need it to make sure that all the fields are filled in before submitting. I'm getting the right pop-ups, but it's still writing the email if the fields are blank after I click OK on the error pop up. I'm hoping one of you will be able to help. I've never done this before and I'm reasonably confident I'm doing it the least effective way possible

Private Sub CommandButton1_Click()


    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Letter of Experience Request Form" & vbNewLine & vbNewLine & _
              "Client Name: " & ClientName.Value & vbNewLine & _
              "Policy Number: " & PolicyNumber.Value & vbNewLine & _
              "Vehicle Details: " & Vehdesc.Value & vbNewLine & _
              "Business Use Start: " & BUstart.Value & vbNewLine & _
              "Business Use End: " & BUend.Value & vbNewLine & _
              "Distance from Home to Office (km): " & Distance.Value & vbNewLine & _
              "Send Letter to This Email: " & Email.Value & vbNewLine & _
              ""

Dim ErrText As String
ErrText = "Please complete following Fields..." & vbCr

If ClientName.Value = "" Then
ErrText = ErrText & vbCr & "- Client Name"
Else
End If

If PolicyNumber.Value = "" Then
ErrText = ErrText & vbCr & "- Policy Number"
Else
End If

If Vehdesc.Value = "" Then
ErrText = ErrText & vbCr & "- Vehicle Description"
Else

If BUstart.Value = "" Then
ErrText = ErrText & vbCr & "- Business Use Start Date"
Else

If BUend.Value = "" Then
ErrText = ErrText & vbCr & "- Business Use End Date"
Else

If Distance.Value = "" Then
ErrText = ErrText & vbCr & "- One Way Distance from Home to Office"
Else

If Email.Value = "" Then
ErrText = ErrText & vbCr & "- Send Letter to This Email"
Else

End If

                  On Error Resume Next
    With xOutMail
        .To = "email@email.com"
        .CC = ""
        .BCC = ""
        .Subject = "Business Use Letter Request"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

 

[Micron]

If any test fails you need to Exit Sub. Doing it in multiple checks might be tedious for your users though, as they'll fix one, get a prompt, fix that, get a prompt and so on. There is a much slicker way in that you gather all that fail validation into a string and present one message, then exit.

 

[infinikate]

If any test fails you need to Exit Sub. Doing it in multiple checks might be tedious for your users though, as they'll fix one, get a prompt, fix that, get a prompt and so on. There is a much slicker way in that you gather all that fail validation into a string and present one message, then exit.

I'm a big fan of a slicker solution, and I appreciate the feedback, but I don't code. Are you able to tell me what to adjust in the code above or what to replace with what?

 

[Micron]

This is way easier in Access because a control with an attached label is easily retrieved and the label caption is used in the message. Excel doesn't have that feature so it will take a bit more coding. Can you start with putting something in the Tag property for the controls you want to validate? Perhaps "reqd" (for "required") for all of them. You would not do this for command buttons or labels. If you add the string for the message after a separator (e.g. semicolon) that will simplify the whole thing.

Busy cooking dinner now so this will have to wait a bit. Post back or pm me to let me know how you get on with the tags.

 

[Micron]

In editing, I lost my example. For Vehdesc the tag might look like reqd;Vehicle Description
No quotes in the tag property.