VBA to Make SHeet Name Equal Cell in Sheet

The_T

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:

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

MrExcel MVP, Moderator
How about
Code:
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

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:

Fluff

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?
 

The_T

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:

=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

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.
 

The_T

New Member
I see.

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

Thanks.
 

Fluff

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

The_T

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#...
Top