VBA to Make SHeet Name Equal Cell in Sheet


New Member
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:


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?




MrExcel MVP, Moderator
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
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
End Sub


New Member
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.
Last edited:


MrExcel MVP, Moderator
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?


New Member
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:


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?


MrExcel MVP, Moderator
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.


New Member
I see.

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



MrExcel MVP, Moderator
Just change the first line of the original code to
Private Sub Worksheet_Calculate()


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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...