Using Worksheet_change module in protected workbook.
 

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?

Using Worksheet_change module in protected workbook.
 

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" wrote in message
...
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?

Using Worksheet_change module in protected workbook.
 

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" wrote:

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" wrote in message
...
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?

Using Worksheet_change module in protected workbook.
 

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
"JDaywalt" wrote in message
...
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" wrote:

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" wrote in message
...
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?