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]

I'm afraid it doesn't seem to do anything.

Is there a way I can send you my excel sheet + an axplanation?

 

[Fluff]

You can upload the file to a share site, such as OneDrive, GoogleDrive, DropBox. Mark it for sharing & then post the link to the thread.

 

[The_T]

I have uploaded it to WeTransfer --> https://we.tl/t-9rn7w3duO6

The 'Master Sheet' in the workbook is actually called 'APPLIANCE ORDER'.

At the top of this sheet, in column D, there is a button which, when clicked, will fill out row 8 from column G onwards.

I want every sheet/tab name following the 'APPLIANCE ORDER' sheet to be named from this row.

Currently, the spreadsheet has the code from the website in my original post in every sheet module. So, if you click the button, then go to one of the next sheets and click anywhere in one, the tab name will update but this requires the user to manually go and click in the sheet.

Needless to say, you'll have to enable macros...

If you'd like me to upload another copy with my mobile number in it so I can explain over the phone, pelase let me know.

 

[Fluff]

I'm afraid it doesn't seem to do anything.

That is because the description you gave & therefore what I suggested, bear absolutely no resemblance to your workbook.
It is always better to give accurate details, rather than generalities.

Will the "Empty" sheets always be called Empty1, Empty2 etc when you run the macro to rename them?

 

[The_T]

Apologies, I am not great at describing what I see in excel with words...

The 'Empty' sheets are the ones I wish to automatically change the names for.

When you click the button at the top of the 'APPLIANCE ORDER' sheet, it fills row 8 on that sheet with 'KT01', 'KT02', KT'03', etc. Currently I think it stops at KT08 or thereabouts...

So I would want the 'Empty1' tab to rename to 'KT01', 'Empty2' to rename to 'KT02', etc.

Ideally this would be done automatically after clicking the button OR there would be another button next to the one currently in column D in the APPLIANCE ORDER sheet that, once clicked, would change the name of all the sheets where a KTXX had been entered into row 8 of the APPLIANCE ORDER sheet.

Does that make sense? I am trying my best.

 

[Fluff]

Yup, I understand that, but the next time you click the Button, will you still have sheets labeled KT01,KT02 etc, or will they be back to Empty1, Empty2 etc?

Or is it safe to simply rename the first sheet after APPLIANCE ORDER, then the 2nd then the 3rd etc?

 

[The_T]

For me, you would only ever click the button more than once if you added a new KTXX. So if KT09 was added to the project, you'd click the button to add it to the APPLIANCE ORDER sheet and the 'Empty9' tab would rename to 'KT09'.

Clicking the button the first time will change the name of the sheets from 'EmptyX' to 'KTXX'. Clicking it again shoulodn't (and currently doesn't) do anything to these sheets unless you remove an existing KTXX or add new KTXXs on if these have been added to the workbook in previous tabs.

So you open the workbook for the first time and you create some KTXXs in one of the sheets before APPLIANCE ORDER. You come to APPLIANCE ORDER and click the button once to bring these KTXXs into row 8. If there are 12 KTXXs ('KT01' to 'KT12'), then 'Empty1' to 'Empty12' would have their names changed to 'KT01' to 'KT12'.

If somebody later adds a 'KT13' to the list in one of the tabs before APPLIANCE ORDER, then clicks the button, 'KT13' would appear after 'KT12' in row 8 in APPLIANCE ORDER and 'Empty13' would change name to 'KT13'.

Does that answer the question?

 

[Fluff]

It does yes, try this mod to your existing macro

Sub Transposing_Column_To_Row()
  Dim arrList As Object, a As Variant, i As Long, [COLOR=#ff0000]j As Long[/COLOR]
  Set arrList = CreateObject("System.Collections.ArrayList")
  a = Sheets("1. DRAWING REG").Range("C19:D" & Sheets("1. DRAWING REG").Range("D" & Rows.Count).End(xlUp).Row)
  [COLOR=#ff0000]j = Sheets("APPLIANCE ORDER").Index[/COLOR]
  For i = 1 To UBound(a)
    If a(i, 1) = "K[COLOR=#00ff00]1[/COLOR]tchen" Then arrList.Add CStr(a(i, 2))
  Next
  arrList.Sort
  [COLOR=#ff0000]For i = 0 To arrList.Count - 1
    Sheets(j + i + 1).Name = arrList(i)
  Next i[/COLOR]
  Sheets("APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
End Sub

BUT you need to make sure that ALL of your hidden sheets are moved so that they are before the APPLIANCE ORDER sheet.

Please note the deliberate typo (in green) otherwise the word will get turned into ***

 

[The_T]

Hi FLuff,

I put the 'AUTOCAD' sheet before the APPLIANCE ORDER sheet (I believe that was the only one between APPLIANCE ORDER and the EMPTIES).

I copy/pasted your code into the sheet module for 'Empty1' in Virtual Basic (only changing the '1' to an 'i' as per the green above).

All that is happening when I click the 'run' icon (the little green 'play' symbol in Virtul Basic') is that I get an error:

- 'Run-time error 1004': Application-defined or object-defined error.

Should I upload the excel workbook with your code in the sheet module for 'Empty1' so that you can see what I'm encountering?

 

[Fluff]

Did you change the typo I flagged in green to an i from a 1?