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