I got 2 ways from this forum a while back ....hope it helps


Option 1:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

on error resume next
sh.Name = sh.Range("a2").Value
if err.number < 0 then
msgbox "Rename failed"
err.clear
end if
on error goto 0
End Sub


Option 2:

Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Application.EnableEvents = False
On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number < 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Range("a2")) Is Nothing Then
Exit Sub
End If

On Error Resume Next
Sh.Name = Sh.Range("a2").Value
If Err.Number < 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0

End Sub

Cheers,
Mayte