VBA to Make SHeet Name Equal Cell in Sheet

The_T

New Member
Joined
Sep 30, 2018
Messages
31
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
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
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
Joined
Sep 30, 2018
Messages
31
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
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
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
Joined
Sep 30, 2018
Messages
31
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
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
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
Joined
Sep 30, 2018
Messages
31
I see.

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

Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
Just change the first line of the original code to
Code:
Private Sub Worksheet_Calculate()
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
Would it be possible to make tab names automatically equal a cell value in another sheet where the value was input manually?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
Yes, but how would you know which sheet to change the name of?
 

Forum statistics

Threads
1,078,515
Messages
5,340,863
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top