VBA to Make SHeet Name Equal Cell in Sheet

[The_T]

Hi everyone,

I used the second VBA code option on the following site to make sheet/tab names equal a cell value within their respective sheets:

https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html

The problem is, the names do not update automatically (as is claimed on the paged).

Instead, you have to click anywhere in the sheet to make the sheet/tab name update.

So, for example, if I want the sheet/tab name at the bottom of my excel page to equal the value in cell A1, and the value in A1 has been changed, I have to click somewhere on the sheet to update the sheet/tab name and this largely defeats the purpose of the code.

Could somebody please help by providing a VBA code that updates the sheet/tab name as soon as the cell value changes without any manual prompting?

Thanks.

T

 

[The_T]

Is there anyway of linking all the sheet modules that have the code from the website to a button so that, when the user clicks the button (made in Visual Basic), the sheet/tab names update themselves (rather than having to go into each individual sheet and click anywhere for the sheet/tab name to update?

I really need all tab names to update in one go base on what they have in their respect A1 cells.

The A1 cells are fed by a row in a master sheet where these are manually entered so maybe there's a macro to update from the row in the master sheet instead?

This is the alst obstacle to something i am preparing for work and I need the tab/sheet names to update automatically...

 

[Fluff]

The A1 cells are fed by a row in a master sheet where these are manually entered so maybe there's a macro to update from the row in the master sheet instead?

As I said before, that possible, but how would you know which sheet to change?

 

[The_T]

I don't know!

Hence why I came here to ask for help.

 

[Fluff]

Where are you inputting the new sheet names?

 

[The_T]

So I have, for example, 5 sheets in my workbook.

The first sheet is entitled 'Master Sheet' and, in Row 1 of this sheet, there are the names I want to update the other sheet tabs with.

So A1 in Master Sheet has 'Orange' written in it, A2 has 'Strawberry', A3 has 'Melon' and A4 has 'Passion Fruit'.

I want the tabs for the other 4 sheets to be named as per this row (for example, the tab for sheet 2 would be named 'Orange').

If I were to change 'Orange' in the Master Sheet to 'Blueberry', I would want the tab for sheet 2 to update with this new name automatically OR, failing that, I would like to create a button in Master Sheet via VBA so that, when clicked, the specified sheets (in this case, 2, 3 ,4 & 5) all update from row A in the Master Sheet.

 

[Fluff]

Ok, would you be happy to change the layout slightly, so it's like

Book1
	A	B
1	Current	New
2	Sheet1	Orange
3	Sheet2	Red
4	Sheet3	Blue
5	Sheet4	Green
Master

And then you just change the values in col B, leaving col A alone (col A can be hidden if needed).

 

[The_T]

The way my sheet is structured means that the fruits/names have to be in a row.

I could use a transposition formula or use '=' in a column in a new sheet to arrange like this but I imagine this would bring the same problem that the formula would not trigger a change event?

Regardless, the names have to first be entered in a row, not a column...

I do have these in a column elsewhere in the workbook but this is because I'm using an array to create the column...

 

[Fluff]

I only laid it out like that, because that's how you described, A1, A2 etc are rows in column A, not columns in row 1.

How about like

Book1
	A	B	C	D	E
1	Current	Sheet1	Sheet2	Sheet3	Sheet4
2	New	Orange	Red	Blue	Green
Master

 

[The_T]

Apologies, you are correct (I have not had much sleep building this monstrosity).

Yes, the above is how things are currently laid out.

Is there a code that would update the sheet names automatically when names are typed into row 2 OR a macro I could assign to a button that once clicked, update selected sheets (in this case, sheets 1, 2, 3 & 4)?

Thanks.

 

[Fluff]

Ok, put this in the Master sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Rows(2)) Is Nothing And Target <> "" Then
        If Evaluate("isref('" & Target.Offset(-1).Value & "'!A1)") And Not Evaluate("isref('" & Target.Value & "'!A1)") Then
            Sheets(Target.Offset(-1).Value).Name = Target.Value
            Target.Offset(-1) = Target
        End If
    End If
End Sub

When you change a name in row 2 it will change the sheet name & also update row 1 with the new name.