Redefining ranges

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

Carl Johnson

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 " &
Sheets(i).name
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
name.
 
Carl Johnson said:
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 " &
Sheets(i).name
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
name.
Click to expand...

Hi Carl, I am sorry I did not understand what you were saying.

Does the Customer Range change? If not, why not just put it explicitly in
your code? Like this:

..Range( "D5:D12" ) <8 Then

You seem to be missing a ) on that formula


The reference to Sheets(i).name was meant to be part of the message box. The
following is all meant to be on one line but maybe there was a word-wrap
problem:
MsgBox " You must fill in all cells - data missing in " & Sheets(i).name

(The idea was that the MsgBox would tell the user which sheet the data was
missing on.)


Does that help at all?

Geoff
 
Instead of inserting a new sheet, then copy|paste, you could have just copied
the previous sheet (Edit|Sheet|more or copy|check Copy in the bottom left
corner).

Then the range name ("Customer") would be on the new sheet.

But if your worksheets are all the same layout (always or almost always!!!), you
could modify the code you and GB created.

Just pick up the address from one of the worksheets that has the "customer"
range defined. (I chose sheet1.)

Option Explicit
Private Sub WorkBook_BeforeClose(Cancel As Boolean)

Dim wks As Worksheet
Dim myAddr As String

myAddr = Worksheets("sheet1").Range("customer").Address

For Each wks In Me.Worksheets
If Application.CountA(wks.Range(myAddr)) _
< wks.Range(myAddr).Cells.Count Then
MsgBox "You must fill in all cells - data missing in " & wks.Name
Cancel = True
End If
Next wks

End Sub

(and sheets(i).name refers to the name (visible on the the worksheet tab of that
sheet.)

(Instead of going through the sheets 1 to N, I cycled through the collection of
worksheets. And instead of using "8", I counted the cells in the Customer
range. (easier to update if you expand/contract that range.))

(If the ranges "customer" vary on any sheet, then you'll have to define ranges
for each sheet. If you include the worksheet name in the name of the range,
then you can use the same "name" on multiple sheets.)

Insert|Name|Define
Names in workbook box: 'Sheet 1'!Customer
(single quotes are required since my sheet name had spaces in it.)
 
Back
Top