Redefining ranges

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

This is an awfully long question (sorry) but in order for someone to know
where I am at, I need to explain, hopefully.
A few days ago I asked the following question.

I used 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? And got the
following response from Geoff.

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 " &
      Cancel = True
      End If
Next i
End Sub

When I inserted a new sheet and copied and pasted the preceding sheet I got
the following error message when I tried to test it.
Run time error 1004 application or object defined error.
Now "Customer" being the range do I need to redefine it for each new sheet
or is there something else I can do. Help! Thank you
Also in the code line Sheets(i).name does name refer to the application