Try placing this code in your ThisWorkbook module. It should rename your sheets as the value in A1 is changed.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo Rename_Error
If Target.Address = "$A$1" And Len(Target.Value) <> 0 Then _
Sh.Name = Target.Value
MsgBox "The sheet could not be renamed. Please check " & vbCrLf & _
"that you did not use illegal characters."