Automate Tab Name to match cell value-CodePudding

So I have this working code to have my tab name = C4 (which also contains a simple formula =IF(Schedule!C5="","#1",Schedule!C5). However, if this cell is changed or updated the tab name doesn't change automatically. Basically user has to go in cell C4 and double click just to hit enter then the tab name updates. Any help to get this to be automatic? Thanks in advance!

Here is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4")) Is Nothing Then
    ActiveSheet.Name = "Daily - " & ActiveSheet.Range("C4")
End If

End Sub

CodePudding user response：

I was able to get this to change on the fly with the following code thanks for the advice!

Private Sub Worksheet_Calculate()
On Error Resume Next
Sheet1.Name = "Daily - " & Range("C4").Value
End Sub

CodePudding user response：

If the change is happening in C5, then Intersect(Target, Range("C4")) will return Nothing because the Target is C5 even though a change did happen in C4 due to formula re-calculation.

To properly check for changes in C4, we need to look at its Precedents, because a change to one of those means that there has been a change in C4. With your formula, C5 is a Precedent of C4.

So we need to check if Target is either C4 itself, or any of C4's Precedents.

Here is your code using the idea of Precedents:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Union(Me.Range("C4"), Me.Range("C4").Precedents)) Is Nothing Then
        Me.Name = "Daily - " & Me.Range("C4")
    End If
End Sub