Change tab name based on cell value containing formula

[yomarcos]

Hi everyone,

I have recently started to use VBA so my knowledge is VERY basic. I found online a way to change a tab name based on cell value but it doesn't work straight away because the cell contains a formula (which is just =Sheet1!A3)

I have been trying to add Sub Worksheet_Calculate() to the code I already have but I keep failing.

Could anyone help me please?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry for this feature." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

Thank you!!

 

[Candyman8019]

try changing:
ActiveSheet.Name = Left(Target, 31)

to

ActiveSheet.Name = Left(Target, 31).Value

 

[Joe4]

Another thing to note. An issue with your code, particularly this line:

Set Target = Range("A1")

In "Worksheet_Change" and "Worksheet_SelectionChange" event procedures, you do NOT define Target - it is one of the parameters being fed in (note it in the first line):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

So it is already defined - it is the range which triggered the code to run.
On a "Worksheet_SelectionChange" event procedure, it would be the range that was just selected.
On a "Worksheet_Change" event procedure, it would be the range that was just manually changed.

So you can use this to watch particular ranges, and run your code thusly.
For example, if you only want to run the code when cell A1 is manually updated, you could put this in your code:

If Target.Address = "$A$1" Then
'    Do something
End If

So then the "Do something" line would only run when cell A1 was manually updated.

The point is, in these kind or event procedures, you do NOT define the Target range - it is already defined inherently.
But you can use that information in your code.

 

[yomarcos]

Another thing to note. An issue with your code, particularly this line:

Set Target = Range("A1")

In "Worksheet_Change" and "Worksheet_SelectionChange" event procedures, you do NOT define Target - it is one of the parameters being fed in (note it in the first line):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

So it is already defined - it is the range which triggered the code to run.
On a "Worksheet_SelectionChange" event procedure, it would be the range that was just selected.
On a "Worksheet_Change" event procedure, it would be the range that was just manually changed.

So you can use this to watch particular ranges, and run your code thusly.
For example, if you only want to run the code when cell A1 is manually updated, you could put this in your code:

If Target.Address = "$A$1" Then
'    Do something
End If

So then the "Do something" line would only run when cell A1 was manually updated.

The point is, in these kind or event procedures, you do NOT define the Target range - it is already defined inherently.
But you can use that information in your code.

Thank you for this. I understand that Target id already defined but I want Target = Range("A1"). Wouldn't I have to set this?

 

[yomarcos]

try changing:
ActiveSheet.Name = Left(Target, 31)

to

ActiveSheet.Name = Left(Target, 31).Value

Thank you but I have tried this already and it doesn't work.

I think I should use Sub Worksheet_Calculate() but I can't figure out how to use it

 

[Joe4]

Thank you for this. I understand that Target id already defined but I want Target = Range("A1"). Wouldn't I have to set this?

NO! You NEVER set the Target range in these kind of procedures.
What the Target range is is what actually called/triggered the procedure to run. Once it is running, it is too late, it has already been sent.

What you can do in the macro is to check to see if the range that called it to run (i.e. "A1") is the range that you are looking for.
If it is, then do your thing. If not, then exit the procedure without doing anything.

See here for more details on how these work (it is important to understand, Event Procedure code is a bit different than the other macros that you may write):

VBA Worksheet Change and SelectionChange Events in Excel

In this tutorial, we’ll discuss the Change and ChangeSelection worksheet events. The Worksheet_Change event-handler procedure executes whenever any cell in the worksheet is changed and Worksheet_SelectionChange event-handler procedure executes when the selection on the worksheet is changed.

www.brainbell.com

If you need help creating code to do what you want, please explain to us in plain English exactly what it is you want to happen.

 

[yomarcos]

NO! You NEVER set the Target range in these kind of procedures.
What the Target range is is what actually called/triggered the procedure to run. Once it is running, it is too late, it has already been sent.

What you can do in the macro is to check to see if the range that called it to run (i.e. "A1") is the range that you are looking for.
If it is, then do your thing. If not, then exit the procedure without doing anything.

See here for more details on how these work (it is important to understand, Event Procedure code is a bit different than the other macros that you may write):

VBA Worksheet Change and SelectionChange Events in Excel

In this tutorial, we’ll discuss the Change and ChangeSelection worksheet events. The Worksheet_Change event-handler procedure executes whenever any cell in the worksheet is changed and Worksheet_SelectionChange event-handler procedure executes when the selection on the worksheet is changed.

www.brainbell.com

If you need help creating code to do what you want, please explain to us in plain English exactly what it is you want to happen.

Thank you Joe4. I'll be reading that later.

What I need is a code that runs automatically and changes every sheet name to the value of A1 of that same sheet. In Sheet 1, I have a list of 50 items and then I have Sheet 2 to Sheet 51 with information about those items. If I change, the list on Sheet 1, A1 of each Sheet updates but the Sheet name doesn't unless I do it manually.
I'm trying to get a code that works like a formula: when you change a value, it updates immediately after hitting enter and automatically.

I have seen online that Sub Worksheet_Calculate() would work but I can't figure out how to use it

I would really appreciate the help

 

[Joe4]

The issue with "Worksheet_Calculate" is that all it knows is that some value was re-calculated on the sheet, but it has no idea which value was. You cannot identify that.

I think you should be able to do it with a "Worksheet_Change" on Sheet1, so as you change the values on that sheet, it updates the appropriate sheet names.
In order to do that, we would just need to know:
1. Exactly what is the range do these 50 values appear in (please provide range addresses)?
2. Are the sheets ordered in the exact same order as they appear in the list?
3. Is the first value on the list the name of Sheet1 (the sheet the list is located in), or is it the name of the first sheet after that?

 

[yomarcos]

The issue with "Worksheet_Calculate" is that all it knows is that some value was re-calculated on the sheet, but it has no idea which value was. You cannot identify that.

I think you should be able to do it with a "Worksheet_Change" on Sheet1, so as you change the values on that sheet, it updates the appropriate sheet names.
In order to do that, we would just need to know:
1. Exactly what is the range do these 50 values appear in (please provide range addresses)?
2. Are the sheets ordered in the exact same order as they appear in the list?
3. Is the first value on the list the name of Sheet1 (the sheet the list is located in), or is it the name of the first sheet after that?

I thought about that but I was worried about point 2

1. Project!A5:54 is the list values that need to become tab names. The book has 51 sheets: Project, Feature 1, Feature 2, ..., Feature 50
2. They are but the order could change once the names start changing. This is why I used A1 of each sheet instead of the value of the cell in Project
3. The first value is the 2nd sheet (Feature 1). It also has a header and a title. This is why the list starts in A5

Also, the way I have it done, I need to use the same code for each sheet, so copy and paste 50 times. Is there a way to use this code for every sheet?

 

[Joe4]

Also, the way I have it done, I need to use the same code for each sheet, so copy and paste 50 times. Is there a way to use this code for every sheet?

No need. We are controlling everything from your Project sheet. As you update a value in range A5:A54, it should update the appropriate sheet.
So the logic assumes that:
- cell A5 is used to name Sheet2
- cell A6 is used to name Sheet3
- cell A7 is used to name Sheet4
...
- cell A54 is used to name Sheet51

Then the only code you would need is this code inthe Project sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long

'   See if updated happened in range of A5:A54
    Set rng = Intersect(Target, Range("A5:A54"))
  
'   If no updates to watched range, exit sub
    If rng Is Nothing Then Exit Sub
  
'   Loop through cells just updated
    For Each cell In rng
'       Capture row of cell updated
        r = cell.Row
'       Update name of appropriate sheet (r-3)
        On Error GoTo err_chk
        Sheets(r - 3).Name = Left(Sheets(r - 3).Range("A1"),31)
        On Error GoTo 0
    Next cell

    Exit Sub
  
err_chk:
'   Error handling code
    MsgBox Err.Number & ":" & Err.Description

End Sub