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

On Jan 2, 5:12 am, 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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Const sNAMECELL As String = "A3"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

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

I don't think that this will work.

Unless the workbook's window has some sheets grouped, the activesheet will be
Setup and the code will be renaming that Setup sheet.

ps. It's probably a good idea to qualify those range variables.

If Not Intersect(.Cells, sh.Range(sNAMECELL)) Is Nothing Then
sSheetName = sh.Range(sNAMECELL).Value

It could stop problems when sheets are grouped.

fujing1003 wrote:

<<snipped

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Const sNAMECELL As String = "A3"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

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

--

Dave Peterson

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

On Jan 1, 6:44 pm, Dave Peterson wrote:
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

Dave thanks this work great except for a couple of things. The first
time I ran the code it worked perfect. Now when I make a change in A3
I get a message Invalid worksheet name in cell A3 but the name is
changed correctly.

second I tried copy the code to a second line and changed A3 to A4 and
changed sheet2.name to sheet3.name. When I change cell A4 I get a
compilation error Ambiguous name detected: Worksheet_Change. What am I
doing wrong. Here is a copy of the code

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
Sheet2.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A4"
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
Sheet3.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

For the first problem, you'll have to share what you typed into A3. My bet is
that you may think that it's valid, but excel KNOWS that it's not. (Is it a
date or time? They can cause problems.)

Second, you only get one of those events. You have to combine the code into one
procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const sNAMECELL1 As String = "A3"
Const sNAMECELL2 As String = "A4"
Const sERROR As String = "Invalid worksheet name in cell "

Dim sSheetName As String
Dim mySheet As Object

With Target

'only one cell at a time
If .Cells.Count 1 Then Exit Sub

If Not (Intersect(.Cells, Me.Range(sNAMECELL1)) Is Nothing) Then
'in A3
Set mySheet = Sheet2
ElseIf Not (Intersect(.Cells, Me.Range(sNAMECELL2)) Is Nothing) Then
'in A4
Set mySheet = Sheet3
Else
'not in either cell
Exit Sub
End If

sSheetName = .Value 'or .text if you have it formatted nicely

If Not sSheetName = "" Then
On Error Resume Next
mySheet.Name = sSheetName
If Err.Number < 0 Then
MsgBox sERROR & .Address(0, 0)
End If
On Error GoTo 0
End If
End With
End Sub

If you are using a date or time, but you have the cell formatted nicely (no
slashes, no backslashes, no colons, no anything bad), then you could use .text
instead of .value in the rename statement.

ps.

You may have noticed that most regulars are top posters. (Yes, it's different
than most newsgroups.) Personally, I find it easier to follow the thread when
people top post.

You may want to conform to what most of the regulars do.

wrote:

<<snipped
Dave thanks this work great except for a couple of things. The first
time I ran the code it worked perfect. Now when I make a change in A3
I get a message Invalid worksheet name in cell A3 but the name is
changed correctly.

second I tried copy the code to a second line and changed A3 to A4 and
changed sheet2.name to sheet3.name. When I change cell A4 I get a
compilation error Ambiguous name detected: Worksheet_Change. What am I
doing wrong. Here is a copy of the code

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
Sheet2.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A4"
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
Sheet3.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

--

Dave Peterson

This is really great. Thanks for your help and thanks for the advice
on top posting except I can't figure out how to top post. I was using
Firefox and going on goggle newsgroups for the posting. I can't find
any settings anywhere for top posting unless putting this at the top
instead of the bottom is what you mean.

I figured out what the error was I didn't make all of the changes
correctly. I am not using dates as tab names only text. I have created
an excel workbook for keeping track of volley stats. I have tabs for
16 tournaments and 15 players. Each tournament name and player name
and number are entered on the setup page. To make it easier to go to
the correct tournament or player tab I have created hyperlinks. I also
wanted to automatically update the sheet name so a user could quickly
go to a tournament or player by clicking on a tab that had the name
of the tournament or player. I know that I can create 21 constants and
1 If Not and 20 ElseIf Not sections but I'm sure that their must be a
better way. I just stated learning visual basic last week. I have just
learned since my first post that you can use a constant just once, And
the difference in sheets vs. sheet. You have been really helpful.


On Jan 7, 7:47 am, Dave Peterson wrote:
For the first problem, you'll have to share what you typed into A3. My bet is
that you may think that it's valid, but excel KNOWS that it's not. (Is it a
date or time? They can cause problems.)

Second, you only get one of those events. You have to combine the code into one
procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const sNAMECELL1 As String = "A3"
Const sNAMECELL2 As String = "A4"
Const sERROR As String = "Invalid worksheet name in cell "

Dim sSheetName As String
Dim mySheet As Object

With Target

'only one cell at a time
If .Cells.Count 1 Then Exit Sub

If Not (Intersect(.Cells, Me.Range(sNAMECELL1)) Is Nothing) Then
'in A3
Set mySheet = Sheet2
ElseIf Not (Intersect(.Cells, Me.Range(sNAMECELL2)) Is Nothing) Then
'in A4
Set mySheet = Sheet3
Else
'not in either cell
Exit Sub
End If

sSheetName = .Value 'or .text if you have it formatted nicely

If Not sSheetName = "" Then
On Error Resume Next
mySheet.Name = sSheetName
If Err.Number < 0 Then
MsgBox sERROR & .Address(0, 0)
End If
On Error GoTo 0
End If
End With
End Sub

If you are using a date or time, but you have the cell formatted nicely (no
slashes, no backslashes, no colons, no anything bad), then you could use .text
instead of .value in the rename statement.

ps.

You may have noticed that most regulars are top posters. (Yes, it's different
than most newsgroups.) Personally, I find it easier to follow the thread when
people top post.

You may want to conform to what most of the regulars do.





wrote:

<<snipped
Dave thanks this work great except for a couple of things. The first
time I ran the code it worked perfect. Now when I make a change in A3
I get a message Invalid worksheet name in cell A3 but the name is
changed correctly.

second I tried copy the code to a second line and changed A3 to A4 and
changed sheet2.name to sheet3.name. When I change cell A4 I get a
compilation error Ambiguous name detected: Worksheet_Change. What am I
doing wrong. Here is a copy of the code

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
Sheet2.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A4"
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
Sheet3.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

--

Dave Peterson

First, thank you for top-posting. That's exactly what I meant.

I'm not quite sure I understand the question, though.

If you want a quick way to go to different worksheets, you could use a toolbar
that allows you to select the one you want.

Here's a macro from Debra Dalgleish's site:
http://contextures.com/xlToolbar01.html

As for renaming sheets, I'd drop the worksheet_change event and use a dedicated
macro.

You could base it on the sheet number (reading the tabs from the left to the
right) or the codename. I'm not sure of the layout of your workbook, though.

wrote:

This is really great. Thanks for your help and thanks for the advice
on top posting except I can't figure out how to top post. I was using
Firefox and going on goggle newsgroups for the posting. I can't find
any settings anywhere for top posting unless putting this at the top
instead of the bottom is what you mean.

I figured out what the error was I didn't make all of the changes
correctly. I am not using dates as tab names only text. I have created
an excel workbook for keeping track of volley stats. I have tabs for
16 tournaments and 15 players. Each tournament name and player name
and number are entered on the setup page. To make it easier to go to
the correct tournament or player tab I have created hyperlinks. I also
wanted to automatically update the sheet name so a user could quickly
go to a tournament or player by clicking on a tab that had the name
of the tournament or player. I know that I can create 21 constants and
1 If Not and 20 ElseIf Not sections but I'm sure that their must be a
better way. I just stated learning visual basic last week. I have just
learned since my first post that you can use a constant just once, And
the difference in sheets vs. sheet. You have been really helpful.

On Jan 7, 7:47 am, Dave Peterson wrote:
For the first problem, you'll have to share what you typed into A3. My bet is
that you may think that it's valid, but excel KNOWS that it's not. (Is it a
date or time? They can cause problems.)

Second, you only get one of those events. You have to combine the code into one
procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const sNAMECELL1 As String = "A3"
Const sNAMECELL2 As String = "A4"
Const sERROR As String = "Invalid worksheet name in cell "

Dim sSheetName As String
Dim mySheet As Object

With Target

'only one cell at a time
If .Cells.Count 1 Then Exit Sub

If Not (Intersect(.Cells, Me.Range(sNAMECELL1)) Is Nothing) Then
'in A3
Set mySheet = Sheet2
ElseIf Not (Intersect(.Cells, Me.Range(sNAMECELL2)) Is Nothing) Then
'in A4
Set mySheet = Sheet3
Else
'not in either cell
Exit Sub
End If

sSheetName = .Value 'or .text if you have it formatted nicely

If Not sSheetName = "" Then
On Error Resume Next
mySheet.Name = sSheetName
If Err.Number < 0 Then
MsgBox sERROR & .Address(0, 0)
End If
On Error GoTo 0
End If
End With
End Sub

If you are using a date or time, but you have the cell formatted nicely (no
slashes, no backslashes, no colons, no anything bad), then you could use .text
instead of .value in the rename statement.

ps.

You may have noticed that most regulars are top posters. (Yes, it's different
than most newsgroups.) Personally, I find it easier to follow the thread when
people top post.

You may want to conform to what most of the regulars do.





wrote:

<<snipped
Dave thanks this work great except for a couple of things. The first
time I ran the code it worked perfect. Now when I make a change in A3
I get a message Invalid worksheet name in cell A3 but the name is
changed correctly.

second I tried copy the code to a second line and changed A3 to A4 and
changed sheet2.name to sheet3.name. When I change cell A4 I get a
compilation error Ambiguous name detected: Worksheet_Change. What am I
doing wrong. Here is a copy of the code

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
Sheet2.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A4"
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
Sheet3.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

--

Dave Peterson

--

Dave Peterson

On Jan 1, 6:44 pm, Dave Peterson wrote:
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

Dave thanks this work great except for a couple of things. The first
time I ran the code it worked perfect. Now when I make a change in A3
I get a message Invalid worksheet name in cell A3 but the name is
changed correctly.

second I tried copy the code to a second line and changed A3 to A4 and
changed sheet2.name to sheet3.name. When I change cell A4 I get a
compilation error Ambiguous name detected: Worksheet_Change. What am I
doing wrong. Here is a copy of the code

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
Sheet2.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A4"
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
Sheet3.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheet1.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub