CODEWITHSUNDEEP
excelvbaexcel-2013
Marien Gébé
Marien Gébé

Reputation: 3

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

Upvotes: 0

Views: 9119

Answers (3)

Imran Malek
Imran Malek

Reputation: 1719

This is an alternate answer if someone still wants to execute this on worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    Dim formulacell As Range
    Set formulacell = Range("C9")
    Set formulacell = Application.Union(formulacell, formulacell.Precedents)

    If Not Intersect(Target, formulacell) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("C9").Value
    End If

    Application.EnableEvents = True
End Sub

Upvotes: 0

Max
Max

Reputation: 759

this should work:

replace

ActiveSheet.Name = ActiveSheet.Range("C9")

by

ActiveSheet.Name = ActiveSheet.Range("C9").Value

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 2

Related Questions