Prevent Printing if Two Cells are blank

[sarahlynnette]

Hi, I'm new to VBA and most of what I have learned has been through Google and I have found a lot on this site. I am trying to write a code that will prevent the user from printing the worksheet if two specific cells are blank. I have tried the following codes, none of which have worked. The first code didn't work due to once the first cell is completed it won't check the others and prevent printing. The second allows the worksheet to print regardless of whether it is blank or not. Could this be because I have a formula in those cells?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("JULY")
If .Range("I2") = "" Then
.Select
.Range("I2").Select
MsgBox "Please enter a dollar amount into the Rate/Hr field"
If .Range("F40") = "" Then
.Select
.Range("F40").Select
MsgBox "Please enter time worked into the proper days"
End If
End With
End Sub


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "JULY" Then
If Application.WorksheetFunction.CountA(Sheets("JULY").Range("F40, I2")) < 2 Then
MsgBox "One or more of the required fields is blank."
Cancel = True
End If
End If
End Sub

 

[Redwolfx]

Try:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
     With Sheets("JULY")
          If .Range("I2") = "" Or .Range("F40") = "" Then
               .Select
               .Range("I2").Select
               MsgBox "One or more of the required fields is blank"
               Cancel = True
          End If
     End With
End Sub

 

[sarahlynnette]

Thanks, Red. But it still prints once I2 has a value in it and F40 is still blank. I need it to prevent printing if either is blank... Any other suggestions?

 

[Redwolfx]

Thanks, Red. But it still prints once I2 has a value in it and F40 is still blank. I need it to prevent printing if either is blank... Any other suggestions?

Is F40 a formula?

I tested this and it worked for me

 

[sarahlynnette]

Yes, it has a formula in it as does I2.

 

[sarahlynnette]

The formula is F40 does allow a zero where I2 does not, could this be the issue?

 

[sarahlynnette]

That was the hold up!! I got it to work with your code and tweeking it a little with a less than sign rather than equal to. Thanks so much for your help

 

[Redwolfx]

Glad to Help. Thanks for the feedback