|Posted by:||Carl Johnson (bjohns…@woh.rr.com)|
|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
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.