Required Fields in Excel Application?

N

Nash13

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
 
T

Tom Ogilvy

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
 
B

Brotha Lee

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
 
N

Nash13

Great. Thank you both. I took those examples and made D14 dependent on D13.
Awesome.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top