The following will work for Sheet1 with the tab name in cell A1.

Paste the following code into the code section of Sheet1:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
sName = Sheets(1).Range("A1")
If sName = "" Then sName = "Sheet1"
Sheets(1).Name = sName
End Sub