Can a worksheet (tab) be renamed via a cell's value?

[Michael Lanier]

I would like to be able to rename a worksheet according to the
returned value of a cell. For example, if Sheet1 A1=1, then Sheet2 is
named "Red." When the same Sheet1 A1=2, then Sheet2 is renamed
"Green." Is this possible? Thanks for your help.

Michael

 

[slarbie]

Right-click your Sheet1 tab and select "View Code". Then paste in the code
below. Add more "cases" for more number-color combinations.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo No2
If Target.Address = "$A$1" Then
Select Case Target.Value
Case 1
Sheets(2).Name = "Red"
Case 2
Sheets(2).Name = "Green"
End Select
End If

No2:
On Error GoTo 0

End Sub

 

[arjen van...]

Another way to use a worksheet change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sheet1.Range("A1").Value = 1 Then
Sheet2.Name = "Red"
ElseIf Sheet1.Range("A1").Value = 2 Then
Sheet2.Name = "Green"
Else
MsgBox ("no name for that value")
End If

End Sub

 

[slarbie]

That works too - but it does seem like a good idea to account for the
possibility there is no Sheet2, thus the error handler I included...

Another thought might be to put some validation on the a1 input cell...