'un modulo di classe
Option Explicit

Private WithEvents xlApp As Application
Private WithEvents xlSheet As Worksheet

Private CurrSheet As Worksheet
Private CurrSheetName As String


Private Sub Class_Initialize()
    Set xlApp = Excel.Application
    Set CurrSheet = ActiveSheet
    Set xlSheet = ActiveSheet
    CurrSheetName = CurrSheet.Name
End Sub

Private Sub Class_Terminate()
    Set xlApp = Nothing
End Sub


Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call changed_name
End Sub

Private Sub xlSheet_SelectionChange(ByVal Target As Range)
    Call changed_name
End Sub


Private Sub changed_name()
    If CurrSheetName <> CurrSheet.Name Then
        MsgBox "Nome foglio cambiato da " & CurrSheetName & " a " & CurrSheet.Name
    End If
    Set CurrSheet = ActiveSheet
    CurrSheetName = CurrSheet.Name
End Sub



'nel codice di ThisWorkbook
Option Explicit

Private xlc As Classe1

Sub Workbook_Open()
    Set xlc = New Classe1
End Sub

Nel modulo di classe di ThisWorkbook (Questa_cartella_di_lavoro):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Call VerificaNome(Sh)
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Call VerificaNome(Sh)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  Call VerificaNome(Sh)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Call VerificaNome(Sh)
End Sub




In un modulo standard:

Public Sub VerificaNome(ByRef shSUT As Worksheet)
  With shSUT
    If .CodeName <> shSUT.Name Then
      MsgBox "il foglio " & .CodeName & vbCrLf _
        & "era stato rinominato in " & .Name & vbCrLf _
        & "Ho ripristinato il nome originale!"
        .Name = .CodeName
    End If
  End With
End Sub