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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Just change the first line of the original code to
Code:
Private Sub Worksheet_Calculate()
 
Upvote 0
Would it be possible to make tab names automatically equal a cell value in another sheet where the value was input manually?
 
Upvote 0
Yes, but how would you know which sheet to change the name of?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top