Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not globally
within the workbook), and I'm stumped. Here's what I typed in under "This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under "This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj

See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under "This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj

Tenaj

I'm still not sure what you want, but if it's to change the sheet tab name
(or at least try to) each time you move the selection on a sheet then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this will fire
far less often and will change the sheet tab name to whatever is in the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I
had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes
on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj

Nick - the Sheet_Activate event is exactly what I want to do. Would you be
able to script the formula for me so that I can copy/paste ???
--
tenaj


"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet tab name
(or at least try to) each time you move the selection on a sheet then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this will fire
far less often and will change the sheet tab name to whatever is in the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I
had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes
on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj

I'm not Nick, but I bet he has a couple of questions.

I know I do.

Are you trying to change the names back after the user changed them? If you
are, did you know that you can protect the workbook (tools|Protection|Protect
workbook and check Structure).

This will stop users from adding/deleting/renaming/moving worksheets (so it
might be too much).

If you can't live with that, what sheet holds the D11:D13 that contains the
new(?) names?

I guessed the sheet with codename Sheet1 in this sample code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myCell As Range

Set myCell = Nothing

Select Case LCase(Sh.CodeName)
Case Is = "sheet4"
Set myCell = Sheet1.Range("D11")
Case Is = "sheet5"
Set myCell = Sheet1.Range("D12")
Case Is = "sheet6"
Set myCell = Sheet1.Range("D13")
End Select

If myCell Is Nothing Then
'do nothing
Else
On Error Resume Next
Sh.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & Sh.Name & " failed"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

If this doesn't work the way you want, post back a few more details and I'm sure
you'll get help.

Tenaj wrote:

Nick - the Sheet_Activate event is exactly what I want to do. Would you be
able to script the formula for me so that I can copy/paste ???
--
tenaj

"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet tab name
(or at least try to) each time you move the selection on a sheet then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this will fire
far less often and will change the sheet tab name to whatever is in the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I
had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes
on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj







--

Dave Peterson

Hey Dave - you're probably right about Nick, so hopefully this will help both
of you to help me.

I have a summary sheet where I calculate monthly totals for a quarterly
report. What I'd like to do is automatically change the sheet name tab when
I type the respective month into cell d11 (lets say January), d12 (February)
and d13 (March) for the first quarter and then (April, May, June, etc.) on
my summary page. That way the names on the tabs will automatically update to
whatever I type into the 3 specific cells.
--
tenaj


"Dave Peterson" wrote:

I'm not Nick, but I bet he has a couple of questions.

I know I do.

Are you trying to change the names back after the user changed them? If you
are, did you know that you can protect the workbook (tools|Protection|Protect
workbook and check Structure).

This will stop users from adding/deleting/renaming/moving worksheets (so it
might be too much).

If you can't live with that, what sheet holds the D11:D13 that contains the
new(?) names?

I guessed the sheet with codename Sheet1 in this sample code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myCell As Range

Set myCell = Nothing

Select Case LCase(Sh.CodeName)
Case Is = "sheet4"
Set myCell = Sheet1.Range("D11")
Case Is = "sheet5"
Set myCell = Sheet1.Range("D12")
Case Is = "sheet6"
Set myCell = Sheet1.Range("D13")
End Select

If myCell Is Nothing Then
'do nothing
Else
On Error Resume Next
Sh.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & Sh.Name & " failed"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

If this doesn't work the way you want, post back a few more details and I'm sure
you'll get help.

Tenaj wrote:

Nick - the Sheet_Activate event is exactly what I want to do. Would you be
able to script the formula for me so that I can copy/paste ???
--
tenaj

"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet tab name
(or at least try to) each time you move the selection on a sheet then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this will fire
far less often and will change the sheet tab name to whatever is in the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I
had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes
on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj







--

Dave Peterson

Then you might as well drop all the other events and just use the
worksheet_change event under the summary tab.

This goes under the Summary worksheet (dump all that other code):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("d11:D13")) Is Nothing Then Exit Sub

Select Case LCase(Target.Address(0, 0))
Case Is = "d11": Set wks = Sheet4
Case Is = "d12": Set wks = Sheet5
Case Is = "d13": Set wks = Sheet6
End Select

On Error Resume Next
wks.Name = Target.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & wks.Name & " failed"
Err.Clear
End If
On Error GoTo 0

End Sub



Tenaj wrote:

Hey Dave - you're probably right about Nick, so hopefully this will help both
of you to help me.

I have a summary sheet where I calculate monthly totals for a quarterly
report. What I'd like to do is automatically change the sheet name tab when
I type the respective month into cell d11 (lets say January), d12 (February)
and d13 (March) for the first quarter and then (April, May, June, etc.) on
my summary page. That way the names on the tabs will automatically update to
whatever I type into the 3 specific cells.
--
tenaj

"Dave Peterson" wrote:

I'm not Nick, but I bet he has a couple of questions.

I know I do.

Are you trying to change the names back after the user changed them? If you
are, did you know that you can protect the workbook (tools|Protection|Protect
workbook and check Structure).

This will stop users from adding/deleting/renaming/moving worksheets (so it
might be too much).

If you can't live with that, what sheet holds the D11:D13 that contains the
new(?) names?

I guessed the sheet with codename Sheet1 in this sample code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myCell As Range

Set myCell = Nothing

Select Case LCase(Sh.CodeName)
Case Is = "sheet4"
Set myCell = Sheet1.Range("D11")
Case Is = "sheet5"
Set myCell = Sheet1.Range("D12")
Case Is = "sheet6"
Set myCell = Sheet1.Range("D13")
End Select

If myCell Is Nothing Then
'do nothing
Else
On Error Resume Next
Sh.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & Sh.Name & " failed"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

If this doesn't work the way you want, post back a few more details and I'm sure
you'll get help.

Tenaj wrote:

Nick - the Sheet_Activate event is exactly what I want to do. Would you be
able to script the formula for me so that I can copy/paste ???
--
tenaj

"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet tab name
(or at least try to) each time you move the selection on a sheet then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this will fire
far less often and will change the sheet tab name to whatever is in the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I thought I
had
to type a command in 'This Workbook' to get the other command to work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global 'ThisWorkbook'
selection_change event which will fire when *any* cell selection changes
on
*any* sheet, the other is firing any selection change on the sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only (not
globally
within the workbook), and I'm stumped. Here's what I typed in under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj







--

Dave Peterson


--

Dave Peterson

Dave

Thanks for diving in...time zones help, plus fresh back from Seattle and now
off to Shanghai...yippee

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Dave Peterson" wrote in message
...
Then you might as well drop all the other events and just use the
worksheet_change event under the summary tab.

This goes under the Summary worksheet (dump all that other code):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("d11:D13")) Is Nothing Then Exit Sub

Select Case LCase(Target.Address(0, 0))
Case Is = "d11": Set wks = Sheet4
Case Is = "d12": Set wks = Sheet5
Case Is = "d13": Set wks = Sheet6
End Select

On Error Resume Next
wks.Name = Target.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & wks.Name & " failed"
Err.Clear
End If
On Error GoTo 0

End Sub



Tenaj wrote:

Hey Dave - you're probably right about Nick, so hopefully this will help
both
of you to help me.

I have a summary sheet where I calculate monthly totals for a quarterly
report. What I'd like to do is automatically change the sheet name tab
when
I type the respective month into cell d11 (lets say January), d12
(February)
and d13 (March) for the first quarter and then (April, May, June, etc.)
on
my summary page. That way the names on the tabs will automatically
update to
whatever I type into the 3 specific cells.
--
tenaj

"Dave Peterson" wrote:

I'm not Nick, but I bet he has a couple of questions.

I know I do.

Are you trying to change the names back after the user changed them?
If you
are, did you know that you can protect the workbook
(tools|Protection|Protect
workbook and check Structure).

This will stop users from adding/deleting/renaming/moving worksheets
(so it
might be too much).

If you can't live with that, what sheet holds the D11:D13 that contains
the
new(?) names?

I guessed the sheet with codename Sheet1 in this sample code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myCell As Range

Set myCell = Nothing

Select Case LCase(Sh.CodeName)
Case Is = "sheet4"
Set myCell = Sheet1.Range("D11")
Case Is = "sheet5"
Set myCell = Sheet1.Range("D12")
Case Is = "sheet6"
Set myCell = Sheet1.Range("D13")
End Select

If myCell Is Nothing Then
'do nothing
Else
On Error Resume Next
Sh.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & Sh.Name & " failed"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

If this doesn't work the way you want, post back a few more details and
I'm sure
you'll get help.

Tenaj wrote:

Nick - the Sheet_Activate event is exactly what I want to do. Would
you be
able to script the formula for me so that I can copy/paste ???
--
tenaj

"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet
tab name
(or at least try to) each time you move the selection on a sheet
then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this
will fire
far less often and will change the sheet tab name to whatever is in
the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I
thought I
had
to type a command in 'This Workbook' to get the other command to
work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global
'ThisWorkbook'
selection_change event which will fire when *any* cell selection
changes
on
*any* sheet, the other is firing any selection change on the
sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only
(not
globally
within the workbook), and I'm stumped. Here's what I typed in
under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It
worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj







--

Dave Peterson


--

Dave Peterson

I'm not sure if Tenaj has the solution, but we'll find out soon (I bet!).

Have fun(?) in your travels.

Nick Hodge wrote:

Dave

Thanks for diving in...time zones help, plus fresh back from Seattle and now
off to Shanghai...yippee

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Dave Peterson" wrote in message
...
Then you might as well drop all the other events and just use the
worksheet_change event under the summary tab.

This goes under the Summary worksheet (dump all that other code):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("d11:D13")) Is Nothing Then Exit Sub

Select Case LCase(Target.Address(0, 0))
Case Is = "d11": Set wks = Sheet4
Case Is = "d12": Set wks = Sheet5
Case Is = "d13": Set wks = Sheet6
End Select

On Error Resume Next
wks.Name = Target.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & wks.Name & " failed"
Err.Clear
End If
On Error GoTo 0

End Sub



Tenaj wrote:

Hey Dave - you're probably right about Nick, so hopefully this will help
both
of you to help me.

I have a summary sheet where I calculate monthly totals for a quarterly
report. What I'd like to do is automatically change the sheet name tab
when
I type the respective month into cell d11 (lets say January), d12
(February)
and d13 (March) for the first quarter and then (April, May, June, etc.)
on
my summary page. That way the names on the tabs will automatically
update to
whatever I type into the 3 specific cells.
--
tenaj

"Dave Peterson" wrote:

I'm not Nick, but I bet he has a couple of questions.

I know I do.

Are you trying to change the names back after the user changed them?
If you
are, did you know that you can protect the workbook
(tools|Protection|Protect
workbook and check Structure).

This will stop users from adding/deleting/renaming/moving worksheets
(so it
might be too much).

If you can't live with that, what sheet holds the D11:D13 that contains
the
new(?) names?

I guessed the sheet with codename Sheet1 in this sample code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myCell As Range

Set myCell = Nothing

Select Case LCase(Sh.CodeName)
Case Is = "sheet4"
Set myCell = Sheet1.Range("D11")
Case Is = "sheet5"
Set myCell = Sheet1.Range("D12")
Case Is = "sheet6"
Set myCell = Sheet1.Range("D13")
End Select

If myCell Is Nothing Then
'do nothing
Else
On Error Resume Next
Sh.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Rename of: " & Sh.Name & " failed"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

If this doesn't work the way you want, post back a few more details and
I'm sure
you'll get help.

Tenaj wrote:

Nick - the Sheet_Activate event is exactly what I want to do. Would
you be
able to script the formula for me so that I can copy/paste ???
--
tenaj

"Nick Hodge" wrote:

Tenaj

I'm still not sure what you want, but if it's to change the sheet
tab name
(or at least try to) each time you move the selection on a sheet
then yes,
use the single sheet event.

You might want to look at the Sheet_Activate event instead as this
will fire
far less often and will change the sheet tab name to whatever is in
the
range for that sheet.

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
See Nick...I'm confusing myself ;o)

I just want to change the sheet name on sheets 4,5, and 6. I
thought I
had
to type a command in 'This Workbook' to get the other command to
work.
Should I just keep the second half and delete anything from 'This
workbook'
???
--
tenaj


"Nick Hodge" wrote:

Tenaj

What are you trying to achieve?

Currently you are firing the event twice. The global
'ThisWorkbook'
selection_change event which will fire when *any* cell selection
changes
on
*any* sheet, the other is firing any selection change on the
sheet that
contains the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Tenaj" wrote in message
...
Hey Guys -

I'm trying to automate the sheet names on specific sheets only
(not
globally
within the workbook), and I'm stumped. Here's what I typed in
under
"This
Workbook":

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal
Target
As Range)
Sheet4.Name = Range("d11").Value
Sheet5.Name = Range("d12").Value
Sheet6.Name = Range("d13").Value

End Sub

and I typed this into Sheets 4,5 & 6 only.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("d11").Value

End Sub

I'm changing the Range value in sheets 5 & 6 respectively. It
worked
once,
but it keeps erroring out now.

What am I doing wrong ???

--
tenaj







--

Dave Peterson


--

Dave Peterson

--

Dave Peterson