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]

Yes, absolutely.

Want me to upload to WeTransfer?

 

[Fluff]

In that case which line gave the error?

 

[Fluff]

Also I forgot to change the ******* to uppercase after the board stripped it out, did you make that change?

 

[The_T]

Haha nope! So I changed it to all capitals and it works when I click the 'run' icon (green play arrow) in Visual Basic but that is the only way it seems to work.

For example, if I copy/paste your code into the sheet module for Empty1, Empty2 & Empty3, then click on the button in column D in APPLIANCE ORDER to bring the KTXXs into row 8, the tabs still have the 'Empty' names.

Is there anyway of getting the code to work without havinbg to go into Virtual Basic and clicking 'run'? I can't seem to get your code to function any other way other than running it in Virtual Basic...

 

[Fluff]

If you delete the code you had for adding the values to G8 onwards, and then assign the new code to the button, you should be good to go.

You can also delete all the code in the "Empty" sheets, as it's no longer needed.

 

[The_T]

Fluff,

You are a life-saver!

Thank you for putting up with me all day and responding as quickly and concisely as you have.

This is exactly what I need.

I could stop there but, seeing as we're on a roll, is there any way to make it so that, if you manually delete all the KTXXs from row 8 in APPLIANCE ORDER, then press the button again (or another new button), the KTXX tab names would go back to reading 'EmptyX'?

No worries if not, would just allow the user to conveniently start again should errors be made.

 

[Fluff]

Assign this to another button

Sub RenameSheets()
    Dim i As Long, j As Long
    j = Sheets("APPLIANCE ORDER").Index
    For i = 1 To Sheets.Count - j
        If Cells(8, i + 6) = "" Then Sheets(i + j).Name = "EMPTY" & i
    Next i
End Sub

 

[The_T]

You're a hero Fluff.

Thanks a million for your time today.

Don't know what drives you to help somebody out like this for free but thankfully you have!

If you ever need soething translated from French to English, or vice-versa, let me know!

 

[Fluff]

My pleasure & thanks for the feedback

 

[The_T]

Hi Fluff!

How've you been?

Are you able to help me out again regarding one of the VBA codes you sent me?

I'm finding that this one will only cover so many rows in Column C before it tells me 'subscript out of range':

Sub Transposing_Column_To_Row()
Dim arrList As Object, a As Variant, i As Long, j As Long
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)
j = Sheets("APPLIANCE ORDER").Index
For i = 1 To UBound(a)
If a(i, 1) = "*******" Then arrList.Add CStr(a(i, 2))
Next
arrList.Sort
For i = 0 To arrList.Count - 1
Sheets(j + i + 1).Name = arrList(i)
Next i
Sheets("APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
End Sub


Happy to explain further and upload the spreadsheet if you're still about!

Cheers,

T