I have many places that use the sheet name so I'd like to keep stuff consistent when this occurs.
Excel VBA doesn't have a sheet change event so I coded the following in the ThisWorkbook object:
Option Explicit
Public gsEntrySheetName As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveSheet.Name <> gsEntrySheetName Then
UpdateAllReferences gsEntrySheetName, ActiveSheet.Name ' if user says it's ok?
gsEntrySheetName = ActiveSheet.Name
End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
gsEntrySheetName = ActiveSheet.Name
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> gsEntrySheetName Then
UpdateAllReferences gsEntrySheetName, Sh.Name ' if user says it's ok?
gsEntrySheetName = ActiveSheet.Name
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name <> gsEntrySheetName Then
UpdateAllReferences gsEntrySheetName, Sh.Name ' if user says it's ok?
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> gsEntrySheetName Then
UpdateAllReferences gsEntrySheetName, Sh.Name ' if user says it's ok?
gsEntrySheetName = ActiveSheet.Name
End If
End Sub
Sub UpdateAllReferences(sOld, sNew)
MsgBox "Sheet " & sOld & " chged to " & sNew
End Sub
Now all you have to do is replace the code in UpdateAllReferences with whatever your code should do.
