Sheetname Change event

[nightcrawler23]

Hi,
How can i code in vba to run a piece of code whenever a sheetname is changed?

 

[nightcrawler23]

Any ideas

 

[Tom Urtis]

Two possible methods, one from me and one from Jaafar:

http://www.mrexcel.com/forum/showthread.php?t=140560

 

[Marcelo Branco]

Or maybe this

Type in a helper-cell the current-name of your worksheet
Say cell AA1, type Sheet1

And this in the code-page of Sheet1

Private Sub Worksheet_Deactivate()
    On Error Resume Next
    Sheets(Range("AA1").Text).Range("AA1") = Me.Name
    If Err <> 0 Then
        Range("AA1") = Me.Name
        MsgBox "your code here"
    End If
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Sheets(Range("AA1").Text).Activate
    If Err <> 0 Then
        Range("AA1") = Me.Name
        MsgBox "your code here"
    End If
End Sub

M.