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!!

 

[Joe4]

If people may be messing with the order of the sheets so that my assuption won't work for you, then you could use this code, but you would need to put it in EVERY sheet module (other than Project):

Private Sub Worksheet_Calculate()
   
    On Error GoTo err_chk
'   Update sheet name if it changed
    If Me.Name <> Left(Me.Range("A1"),31) Then Me.Name = Left(Me.Range("A1"),31)
    On Error GoTo 0
   
    Exit Sub
   
err_chk:
'   Error handling code
    MsgBox Err.Number & ":" & Err.Description
   
End Sub

Obviously, the other way would be preferrable, as you would only have to put the code in one place as opposed to 50.

 

[yomarcos]

If people may be messing with the order of the sheets so that my assuption won't work for you, then you could use this code, but you would need to put it in EVERY sheet module (other than Project):

Private Sub Worksheet_Calculate()
  
    On Error GoTo err_chk
'   Update sheet name if it changed
    If Me.Name <> Left(Me.Range("A1"),31) Then Me.Name = Left(Me.Range("A1"),31)
    On Error GoTo 0
  
    Exit Sub
  
err_chk:
'   Error handling code
    MsgBox Err.Number & ":" & Err.Description
  
End Sub

Obviously, the other way would be preferrable, as you would only have to put the code in one place as opposed to 50.

This is exactly what I wanted. Thank you very much!!!

 

[Joe4]

You are welcome.
Glad I was able to help.

 

[yomarcos]

Hi Joe4,

I have one more question for you. Is it possible to add something to this code that you sent me that would make impossible for users to change the sheets names manually?

 

[Joe4]

You can use one of the methods mentioned here: How to prevent from changing sheet name in Excel?

Note that if you use the Worksheet protection option, what you would do is add code to all the "Worksheet_Calculate" sub procedures that first unprotects the sheet, makes the name change, and then re-protects the sheet at the end. That is how you make it so that the code can change the sheet names, but no one can manually.

 

[yomarcos]

You can use one of the methods mentioned here: How to prevent from changing sheet name in Excel?

Note that if you use the Worksheet protection option, what you would do is add code to all the "Worksheet_Calculate" sub procedures that first unprotects the sheet, makes the name change, and then re-protects the sheet at the end. That is how you make it so that the code can change the sheet names, but no one can manually.

Thank you!!! Problem solved

 

[Joe4]

You are welcome!