Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$4" Then
Me.Name = Target(1)
End If

End Sub

Regards,
Peter T

"Patrick C. Simonds" wrote in message
...
The code below is intended to change the name of the Active Worksheet to
the
Value in cell A4. But I get an "object or With variable not set"




Private Sub Worksheet_Change(ByVal Target As Range)

Dim Sh As Worksheet

If Target.Address = "$A$4" Then
Const sStr As String = "A4"


ActiveSheet.Name = Sh.Range(sStr).Value


End If

End Sub