Redefining ranges

  • Thread starter Thread starter Carl Johnson
  • Start date Start date
C

Carl Johnson

I use the following code that will not allow an end user to close a
spreadsheet until all specified cells are entered.

Private Sub WorkBook_BeforeClose(Cancel As Boolean)
If Application.WorkSheetFunction.CountA(Sheets("Sheet1") _
.Range( "Customer" ) <8 Then
MsgBox " You must fill in all cells"
Cancel = True
End If
End Sub

Now it works okay with one sheet but we have hundreds of sheets with more
added daily. How can I restructure the code to redefine the ranges for each
sheet ( the sheets being identical ) and that as each new sheet is added it
would be updated to reflect its range and new sheet name? I hope I am making
sense. Thank you.
 
Try this

Private Sub WorkBook_BeforeClose(Cancel As Boolean)

For i= 1 to Sheets.count
If Application.WorkSheetFunction.CountA(Sheets(i) _
.Range( "Customer" ) <8 Then
MsgBox " You must fill in all cells - data missing in " &
Sheets(i).name
Cancel = True
End If
Next i
End Sub

I have assumed that Customer is a named range that applies to any sheet

This will produce a separate msgbox for each sheet with data missing which
could be tiresome but may be what you want.

Geoff
 
Back
Top