Prevent work from closing if "required " is present

[Timmy50]

Hello I am try to pervent excel from closeing unit the correct data is entered, what we need is if there is the word Required in any cell down the B column excel wont close and request the cell be filled out. I have a formula work if anything is entered in the D cells the corresponding b cell we turn red and "required" comes up.


I tryied this but i cant get a range for the entire B column to work.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Sheets("sheet1").Range("B1269").Value = "Required" Then
Cancel = True
MsgBox "Please enter Part Number"
End If
End Sub

Any help is greatly appreciated

 

[Vbagreenhorn1]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Application.CountIf(Sheets("sheet1").Range("B:B"), "Required") > 0 Then
      Cancel = True
      MsgBox "Please enter Part Number"
 End If
 End Sub

 

[Timmy50]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Application.CountIf(Sheets("sheet1").Range("B:B"), "Required") > 0 Then
      Cancel = True
      MsgBox "Please enter Part Number"
 End If
 End Sub

Thank you Sir works perfectly.