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

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) = "A1" Then
        If Target = "" Then Exit Sub
        On Error GoTo Badname
        Me.Name = Left(Target, 31)
        Exit Sub
    End If
Badname:
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

 

[The_T]

I'm afraid that this doesn't seem to do anything.

Should I be posting it in a specific sheet object in Visual Basic (much like I did with the original code), the 'thisworkbook' object or a module?

The original code, once posted in a sheet in Visual Basic, simply starts working when I click in the sheet.

The code you have sent shows no sign of life when I copy/paste into a sheet in VB.

 

[Fluff]

It needs to go in the sheet module, for each sheet you want it to work on. How is the value in A1 being changed?

 

[The_T]

That's what I was doing!

The value is being picked up from a cell in another sheet (example, 'MAIN SHEET').

The code in A1 is currently as follows:

=IF('MAIN SHEET'!G8=0,"EMPTY1",'MAIN SHEET'!G8)

I put this formula in as, without it, cell A1 simply reads '0' and, with the VBA code I used from the website in my original post, I get an area if I am using multiple sheets with the code and they all have their name changed to '0'.

Can I send my excel spreadsheet here?

 

[Fluff]

As A1 is a formula, you would be better off with the code you had originally.
A formula does not trigger the change event, so that won't work. The other option would be a calculate event, but that will trigger every-time any cell is calculated.

 

[The_T]

I see.

How would I modify the VBA code to turn it into a calculate event?

Thanks.

 

[Fluff]

Just change the first line of the original code to

Private Sub Worksheet_Calculate()

 

[The_T]

Would it be possible to make tab names automatically equal a cell value in another sheet where the value was input manually?

 

[Fluff]

Yes, but how would you know which sheet to change the name of?