PDA

View Full Version : How Do I Get An Excel Sheet That Validates And Then Emails?



neosegauk
05-31-2014, 10:44 AM
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/0B8OtUevU1V5sWlc2RDF4T0p6Qzg/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
05-31-2014, 11:41 AM
Would putting Validation on the cells (text length > 3) do what you want?

neosegauk
05-31-2014, 01:03 PM
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
05-31-2014, 11:57 PM
How would I go about doing that?

mikerickson
06-02-2014, 06:36 AM
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