Redefining ranges

Giganews Newsgroups
Subject: Redefining ranges
Posted by:  Carl Johnson (bjohns…
Date: Sat, 11 Oct 2003

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.