How Do I Get An Excel Sheet That Validates And Then Emails?

[neosegauk]

Hope you are all doing well. I have a few questions (please bare with me as I am new to VBA). I am tring to get an excel user form to validate the cells that are not protected. What I am looking to do is make sure each field has over 3 charecters of text. Once these are validated then the user can hit a send button (which will only become active when all fields are completed) and then it to sends an email with the spreadsheet (userform) attached.


I am unsure how to go about doing this. What would be the best steps to take?


Please see the form https://docs.google.com/file/d/0B8Ot...F4T0p6Qzg/edit (this opens in Google drive but can be downloaded by pressing File and download to view what I have done. Including send buton).

(hope this makes sense, if not I am happy to answer any questions)

Thanks.

[mikerickson]

Would putting Validation on the cells (text length > 3) do what you want?

[neosegauk]

Hi Milkerickson

Thanks for the reply. I have looked into doing that under data and then validation and making it so a message appears if they have not entered a specific ammount of text. The only issue is I need them to not be able to skip this without filling it in and for the Send button to only become enabled (which I need to email) once they have entered data into all 3 text boxes.

[neosegauk]

How would I go about doing that?

[mikerickson]

Try putting this in the ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rangeToFill, oneCell As Range, flag As Boolean
    Set rangeToFill = ThisWorkbook.Sheets("Sheet1").Range("A1,C1,A2,C2")
    For Each oneCell In rangeToFill
        If oneCell.Value = vbNullString Then
            'MsgBox "fill cell " & oneCell.Address
            flag = True
            Exit For
        End If
    Next oneCell
        Sheet1.Shapes("Button 1").Visible = Not flag
    
End Sub