How do I trigger a macro to run when a sheet is renamed?-CodePudding

I have a worksheet, and I want to run a macro when the active sheet is renamed (renamed to anything).

The reason for this is as follows:

I have a Pivot table that is filtered based on a cell (D1) value, using VBA. (When the D1 is changed, the pivot table filter changes). D1 is, in turn, determined (matched) by the sheet name, using a formula. (I.e. if the sheet name is RG01, then D1 automatically changes RG01)

The problem is, when the sheet name is changed (and D1 changes with it) it does not trigger a change in the Pivot table filter. It's only when I edit D1 that the Pivot table filter changes. I.e. If I select D1 and re-enter the formula, the Pivot Table filter changes.

So, I'm thinking, I could write a macro that re-enters the formula in D1 whenever the sheet is renamed. I can do most of this by 'recording' a macro, but I don't know how to trigger the macro when the sheet is renamed.

By "re-enter" I mean clicking the cell (D1), clicking the formula bar, then pressing enter.

Apologies, I'm not proficient in VBA or excel so my terminology may be wrong. I'm so close to making my spreadsheet work and this is the final hurdle that I just cannot seem to get around.

Thank you so much is anticipation!

CodePudding user response：

For a project where I wanted to capture sheet renaming I used a public variable for the active sheet name and with a Workbook_SheetCalculate I was capturing the change. (Note that this worked for the active sheet renamed by hand and not programmaticaly renaming other sheets name from a macro. That would probably need a public array/dict with alll the sheet names stored on opening the workbook) A sample code would be:

(ThisWorkbook):

Public LastSheet As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    LastSheet = ActiveSheet.Name

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    If LastSheet <> ActiveSheet.Name Then

        '... do something here

        LastSheet = ActiveSheet.Name

    End If

End Sub

The calculate sub would fire in every change in the active sheet but run the desired code only when the sheet name changes.