Excelで特定行の削除／Undoを検知する試み

0.README.md

[Excel] Sheetモジュールで特定行の削除／Undoを検知するサンプル

→ 類似の試みも参照

ソースコード

1. Sheetモジュールで特定行の削除／Undoを検知するサンプル: Sheet.vba

Sheet.vba

Option Explicit

Private Const ConfigSheetName = "config"
Private WatchCellConfig As Range
Private WatchCellDeleted As Boolean

Private Enum ChangeKind
    Normal = 0
    Deleted = 1
    Undo = 2
End Enum

Private Sub Worksheet_Activate()
    Call UpdateWatchCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call UpdateWatchCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If UpdateWatchCell() Then Exit Sub
    
    Dim ChgKind As ChangeKind: ChgKind = GetChangeKind()
        
    If ChgKind = ChangeKind.Deleted Then
        Debug.Print "* DELETED *", WatchCellConfig.Formula
    ElseIf ChgKind = ChangeKind.Undo Then
        Debug.Print "* UNDO *", WatchCellConfig.Formula, WatchCellConfig.Value
    Else
        Debug.Print "Normal", WatchCellConfig.Formula, WatchCellConfig.Value
    End If
End Sub

Private Function GetChangeKind() As ChangeKind
    GetChangeKind = ChangeKind.Normal
    
    If WatchCellDeleted Then
        If Not IsReferenceError(WatchCellConfig) Then
            WatchCellDeleted = False
            GetChangeKind = ChangeKind.Undo
        End If
    Else
        If IsReferenceError(WatchCellConfig) Then
            WatchCellDeleted = True
            GetChangeKind = ChangeKind.Deleted
        End If
    End If
End Function

Private Function UpdateWatchCell() As Boolean
    If WatchCellConfig Is Nothing Then
        Set WatchCellConfig = Worksheets(ConfigSheetName).Range("A1")
        WatchCellDeleted = IIf(IsReferenceError(WatchCellConfig), True, False)
        UpdateWatchCell = True
        Exit Function
    End If
End Function

Private Function IsReferenceError(TargetRange As Range) As Boolean
    If TargetRange.Formula Like "*![#]REF!" Then IsReferenceError = True
End Function