Required Fields in Excel Application?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel sheet that has a script attached to a button that will email
the form via outlook once the button is pressed. Anyway, is there any way to
make certain fields required before the email is sent and produce an error
message if those fields are left blank? Cells D13 and D14 would be the ones
in question. I have my code below. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
if Range("D13") = "" or Range("D14") = "" then
msgbox "Missing Data, quitting"
exit sub
end if
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Nash,

Add the following code at the beginning of your code

if Sheets("EmailAddresses").range("D13").value = "" or
Sheets("EmailAddresses").range("D14").value = "" then
msgbox "Can not sent, please enter all required field",vbcritical,"Error"
exit sub
end if
 
Back
Top