Change tab name If:

[gheyman]

How do I modify this so that is cell R2 has the word Template in it, the code does not change the tab name?

Private Sub Worksheet_Deactivate()
'Updateby20150602
Dim Ws As Worksheet
Dim rngAddr As String
Dim Name As String
rngAddr = "R1"
For Each Ws In Application.ActiveWorkbook.Sheets
Name = Ws.Range(rngAddr).Value
If Name <> "" Then
Ws.Name = Name
End If
Next

End Sub

Thanks!

 

[Yongle]

try this

Private Sub Worksheet_Deactivate()
    Dim Ws As Worksheet
    Dim rngAddr As String
    Dim Name As String
    rngAddr = "R1"
    For Each Ws In Application.ActiveWorkbook.Sheets
        Name = Ws.Range(rngAddr).Value
            If Name <> "" And LCase(Ws.Range("R2")) <> "template" Then
                Ws.Name = Name
            End If
    Next
End Sub

 

[gheyman]

Thank you

What if I didn't want to check and rename every worksheet and just wanted to do this for active worksheet?

 

[Yongle]

Is Worksheet_Deactivate where you plan to use it?

Private Sub Worksheet_Deactivate()
    Dim shName As String
    shName = Range("R1")
    If Name <> "" And LCase(Range("R2")) <> "template" Then Me.Name = shName
End Sub

Otherwise

Sub TestShName()
    Dim shName As String, ws As Worksheet
    Set ws = ActiveSheet
    shName = ws.Range("R1")
    If Name <> "" And LCase(ws.Range("R2")) <> "template" Then ws.Name = shName
End Sub