VBA to Make SHeet Name Equal Cell in Sheet

The_T

New Member
Joined
Sep 30, 2018
Messages
31
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
 
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...
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
Where are you inputting the new sheet names?
 
Upvote 0
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.
 
Upvote 0
Ok, would you be happy to change the layout slightly, so it's like


Book1
AB
1CurrentNew
2Sheet1Orange
3Sheet2Red
4Sheet3Blue
5Sheet4Green
Master


And then you just change the values in col B, leaving col A alone (col A can be hidden if needed).
 
Upvote 0
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...
 
Upvote 0
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
ABCDE
1CurrentSheet1Sheet2Sheet3Sheet4
2NewOrangeRedBlueGreen
Master
 
Upvote 0
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.
 
Upvote 0
Ok, put this in the Master sheet module
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top