[Excel VBA] ワークシートの変更を検知する　(Worksheet.Change イベント)

公開日： 2015/03/01 : 最終更新日：2015/07/10
カテゴリ: 03-5_ExcelVBA_メモ・小技集
タグ: Excel VBA, Worksheet.Change イベント, イベント

動作確認環境

Windows7
Excel: 2007

構文

式.Change(Target)

1 2	式.Change(Target)

式:Worksheet オブジェクト。

<戻り値>
Nothing
・・・？

Target

Range型、必須
変更された範囲が渡される。複数のセルを渡すことができる。

メモ

・ Worksheet_Change

サンプルコード

1. ワークシートのセルが変更されたことを検知

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Value
    MsgBox Target.Address
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox Target.Value

MsgBox Target.Address

End Sub

2. ワークシートのセルに特定の変更が生じたことを検知

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'セルにAAが入力されたことを検知する。
    If Target.Value = "AA" Then
        MsgBox Target.Value
        MsgBox Target.Address
    End If
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'セルにAAが入力されたことを検知する。

If Target.Value = "AA" Then

MsgBox Target.Value

MsgBox Target.Address

End If

End Sub

3. 特定のセル、セル範囲に変更が生じたことを検知

・Intersectメソッドを使ってTargetが指定範囲にあるかどうかを検知することができます。

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Then
    Else
        Target.Interior.ColorIndex = 6
        MsgBox "指定範囲内の変更です。"
    End If
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:A10")) Is Nothing Then

Else

Target.Interior.ColorIndex = 6

MsgBox "指定範囲内の変更です。"

End If

End Sub

ただし、この場合複数の変更(たとえば複数のコピー&ペースト)にはうまく対応できません。
そこで、方法4。

4-1. 特定のセル、セル範囲に変更が生じたことを検知

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range
'
'    Set C = Application.Intersect(Target, Range("A1:A10"))
        For Each C In Target
            If Application.Intersect(C, Range("A1:A10")) Is Nothing Then
            Else
                C.Interior.ColorIndex = 6
                MsgBox "指定範囲内の変更です。"
            End If
        Next C
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range

' Set C = Application.Intersect(Target, Range("A1:A10"))

For Each C In Target

If Application.Intersect(C, Range("A1:A10")) Is Nothing Then

Else

C.Interior.ColorIndex = 6

MsgBox "指定範囲内の変更です。"

End If

Next C

End Sub

ただし、この方法では、『Ctrl+Aで全セルを選択してコピーする』などのアクションを行うと、全セル(16,384列 x 1,048,576行 = 17,179,869,184セル(約170億セル))に対して、For~Next間のループを繰り返すことになるため注意が必要です。
※マクロを停止させる場合は、エクセル画面で「Ctrl+Pause」または「Ctrl+Break」を押下。

これを回避させるために、『たとえば100個以上の複数のセルの同時編集時はループを回避する』などしてみます。(方法4-2)
※もっとよい回避のアイディアがあるはず・・・。

4-2. 特定のセル、セル範囲に変更が生じたことを検知

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range
'
'ループが止まらないときは、エクセル画面で「Ctrl+Pause」または「Ctrl+Break」を押下。
        If Target.CountLarge <= 100 Then 'Target.Countは2007ではオーバーフローする
            For Each C In Target
                If Application.Intersect(C, Range("A1:A10")) Is Nothing Then
                Else
                    C.Interior.ColorIndex = 6
                    MsgBox "指定範囲内の変更です。"
              End If
            Next C
        End If
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range

'ループが止まらないときは、エクセル画面で「Ctrl+Pause」または「Ctrl+Break」を押下。

If Target.CountLarge <= 100 Then 'Target.Countは2007ではオーバーフローする

For Each C In Target

If Application.Intersect(C, Range("A1:A10")) Is Nothing Then

Else

C.Interior.ColorIndex = 6

MsgBox "指定範囲内の変更です。"

End If

Next C

End If

End Sub

※「Target.Count」のCountプロパティはLong型で、2007の全セル数はLong型の-2,147,483,648～2,147,483,647を超えるためオーバーフローを起こすようです。
2007ではCountLargeプロパティが追加され、これを用いるとオーバーフローを起こさずに全セルカウントすることができます。

課題

N/A

サンプルファイル

N/A

参考

[]Worksheet.Change イベント (Excel)

Message コメントをキャンセル

PREV: [Excel VBA] 任意のセルが、指定したセル範囲にあるか調べる(Application.Intersect メソッド
NEXT: 実行中のマクロを中断する - Excel VBA

[Excel VBA] ワークシートの変更を検知する　(Worksheet.Change イベント)

動作確認環境

構文

メモ

サンプルコード

1. ワークシートのセルが変更されたことを検知

2. ワークシートのセルに特定の変更が生じたことを検知

3. 特定のセル、セル範囲に変更が生じたことを検知

4-1. 特定のセル、セル範囲に変更が生じたことを検知

4-2. 特定のセル、セル範囲に変更が生じたことを検知

課題

サンプルファイル

参考

広告

関連記事

[Excal VBA] オートフィルターを使ってリストにフィルターをかけます。 – Range.AutoFilter メソッド-

[Excel VBA] 改ページプレビュー、ノーマルビュー(標準)、ページレイアウト表示を切り替える -Window.Viewプロパティ-

動的配列と固定配列 – Excel VBA

[Excel VBA] 任意のセルが、指定したセル範囲にあるか調べる(Application.Intersect メソッド

[Excal VBA] セルを挿入する。 – Range.Insert メソッド-

[Excal VBA] セルに値を入れる – Range.Value プロパティ-

03-5 ExcelVBAメモ・小技集 INDEX

[Excel VBA] セルの検索(Range.Findメソッド)

[Excal VBA] セルをアクティブにする。 – Range.Activate メソッド-

[Excel VBA] 印刷時の余白、ヘッダー、フッター等を設定する。

広告

Message コメントをキャンセル

広告

NEW ENTRY

INDEX

カテゴリー

アーカイブ

タグ

最近の投稿

最近のコメント

広告

サイト内検索

INDEX