Visual Basics - Automating Specific Sheet Names

[Guest]

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 ???

 

[Nick Hodge]

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
(e-mail address removed)

 

[Guest]

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'
???

 

[Nick Hodge]

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
(e-mail address removed)

 

[Guest]

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 ???

 

[Dave Peterson]

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 D1113 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.

 

[Guest]

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.

 

[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("d1113")) 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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Dave Peterson]

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

Have fun(?) in your travels.