Prevent work from closing if "required " is present

Timmy50

New Member
Joined
Feb 1, 2018
Messages
2
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,225,743
Messages
6,186,770
Members
453,370
Latest member
juliewar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top