• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

VBA to Make SHeet Name Equal Cell in Sheet

The_T

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,303
Office Version
365
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,303
Office Version
365
Platform
Windows
Where are you inputting the new sheet names?
 

The_T

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,303
Office Version
365
Platform
Windows
Ok, would you be happy to change the layout slightly, so it's like

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Current</td><td style=";">New</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sheet1</td><td style=";">Orange</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sheet2</td><td style=";">Red</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Sheet3</td><td style=";">Blue</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sheet4</td><td style=";">Green</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Master</p><br /><br />

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

The_T

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,303
Office Version
365
Platform
Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Current</td><td style=";">Sheet1</td><td style=";">Sheet2</td><td style=";">Sheet3</td><td style=";">Sheet4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">New</td><td style=";">Orange</td><td style=";">Red</td><td style=";">Blue</td><td style=";">Green</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Master</p><br /><br />
 

The_T

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,303
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,961
Messages
5,337,432
Members
399,145
Latest member
SPLhorses

Some videos you may like

This Week's Hot Topics

Top