Search code examples
excelvbaexcel-2013

EXCEL VBA Dynamic Sheet Name according to a cell value - Not working when formula in the cell

Hej,

I've created a small VBA code to dynamically rename a worksheet.

It's working perfectly when the cell is just manually typed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C9")) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("C9")

    End If
End Sub

But then as soon as I will put a formula concatenating 2 cells values within C9 cell it will not update it automatically. To make it work I need to enter the cell and type ENTER again and it works. I have to do same manipulation each time I change a value in on of the 2 cell concatenated.

THANKS for your help guys

Solution

  • You need to capture a different event:

      Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        ActiveSheet.Name = ActiveSheet.Range("C9")
    Application.EnableEvents = True
 End Sub

    NOTE:

    We disable events during the name change in case the worksheet contains a formula referencing the tab-name.