The worksheet_Change event only looks for changes to the worksheet that owns the
code.

But you can use the worksheet_change event under the Setup worksheet to change
the name of any worksheet in your workbook.

If you want to try, then remove the worksheet_change code (if you added it to
the other sheet). Then add this behind the Setup sheet (rightclick on the Setup
tab and choose view code and then paste this into the code window):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A3"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Sheet1.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub


Notice that the Me keyword has been replaced with Sheet1. Me represents the
object owning the code--in your old code (Bob Phillips' code??), that's the
worksheet being changed.

In this suggested code, I used Sheet1.

That's the name you see in the project explorer when you select your project
Hit ctrl-r to see the project explorer
Expand the project to see the "microsoft excel objects"

You'll see:
Sheet1(somesheetnamehere)
The name in ()'s is the name the user sees on the worksheet tab in excel.
The name in front of that (Sheet1 in this case) is the codename. It's much more
difficult for the users to change this name and it's usually much safer to use
the codename in your VBA code.

wrote:

Can this be modified so a cell that is changed on another worksheet be
the trigger instead of the cell of the current worksheet. for example
cell "A3" on a worksheet named "Setup"? Thanks to anyone that can
help.

Sheetname from cell, automatically

If you want to have your sheet name change when a cell value is
changed, you can use this Worksheet_Change() event macro. Put it in
your worksheet code module. Note that it has minimal error checking.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

Change the value of sNAMECELL to your desired cell.

This page last updated Sunday, 28 November 2004

--

Dave Peterson