Macro to change sheet name when cell value is changed

[nodroj81]

I spent a few minutes searching prev. posts for an answer but no luck because I have a twist to a pretty basic problem.

Here is my situation - I would like to create a macro that will automatically change the name of the sheet any time a value is entered into cell a1. For example, in cell A1 of sheet1 I would input "Hello", and then the tab for sheet1 would be automatically renamed to "Hello".

Here's the twist - the workbook will have multiple sheets, and I want all tabs to reflect the value of a specific cell (a1) in each sheet.

For example:

value in sheet1, cell a1 = "Yes" - corresponding tab name would change to "Yes"
value in sheet2, cell a1 = "No" - corresponding tab name would change to "No"
value in sheet3, cell a1 = "Maybe" - corresponding tab name would change to "Maybe"
etc...

The "tab change" cell would always be cell a1 in each sheet.

Thanks in advance for any help.

 

[JoeMo]

Place this in the Thisworkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, [a1]) Is Nothing Then
    Sh.Name = [a1]
End If
End Sub

 

[nodroj81]

JoeMo,

That worked perfectly - thank you very much for the quick response!