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

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


"Michael Lanier" wrote:

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

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

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

"arjen van..." wrote:

> 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