Using Worksheet_change module in protected workbook.

[JDaywalt]

I have a Worksheet_change module on each sheet tab in a workbook that links
the sheet tab name to a specific cell on the worksheet. Here is the simple
code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("B2").Value
End Sub

The problem is that when the workbook is protected, users get this error
message when attempting to enter data into any of their "unprotected"
regions: "application-defined or object-defined error". The Debug button
goes to the Worksheet_change code--- which confuses me because they are not
entering into cell B2 (it is protected). I'll admit this code is new to me,
but what am I missing?

 

[Otto Moehrbach]

That macro fires whenever any change is made to any cell in the entire sheet
and that is what is causing that error. If you want the code within that
macro (the sheet name code) to execute only when B2 is changed, you must
enter a line similar to the following before your sheet name code:
"If Not Intersect(Target, Range("B2")) is Nothing Then _" without the
quotes. HTH Otto

 

[JDaywalt]

Absolutely perfect! (Just had to remember to also add the "Else ... End If"
at the bottom of my sheet code!!)

Thanks for your help!

 

[Otto Moehrbach]

The code line I sent you doesn't require an Else or End If. The space and
underline character at the end of the line tells Excel that those "2" lines
are to be taken as one line. HTH Otto