[Excel VBA] ワークシートの変更を検知する (Worksheet.Change イベント)
公開日:
:
最終更新日:2015/07/10
カテゴリ: 03-5_ExcelVBA_メモ・小技集
タグ: Excel VBA, Worksheet.Change イベント, イベント
動作確認環境
Windows7
Excel: 2007
構文
1 2 |
式.Change(Target) |
式:Worksheet オブジェクト。
<戻り値>
Nothing
・・・?
Target | Range型、必須 変更された範囲が渡される。複数のセルを渡すことができる。 |
メモ
・ Worksheet_Change
サンプルコード
1. ワークシートのセルが変更されたことを検知
1 2 3 4 5 6 |
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value MsgBox Target.Address End Sub |
2. ワークシートのセルに特定の変更が生じたことを検知
1 2 3 4 5 6 7 8 9 |
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が指定範囲にあるかどうかを検知することができます。
1 2 3 4 5 6 7 8 9 |
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. 特定のセル、セル範囲に変更が生じたことを検知
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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. 特定のセル、セル範囲に変更が生じたことを検知
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
参考
広告
関連記事
-
-
[Excel VBA] セルの検索(Range.Findメソッド)
動作確認環境 Windows7 Excel: 2007 構文 ・検索範囲の
-
-
[Excal VBA] セルを挿入する。 – Range.Insert メソッド-
動作確認環境 Windows7 Excel: 2007, 2013 説明 指定された範囲
-
-
[Excel VBA] 任意のセルが、指定したセル範囲にあるか調べる(Application.Intersect メソッド
動作確認環境 Windows7 Excel: 2007 構文 式: Appl
-
-
[Excel VBA] メッセージボックスを使う
動作確認環境 Windows10 Excel: 2016 説明 メッセージボックスを使う
-
-
[Excal VBA] セルに値を入れる – Range.Value プロパティ-
動作確認環境 Windows7 Excel: 2007 説明 セルに値をいれます。
-
-
[Excal VBA] セルをアクティブにする。 – Range.Activate メソッド-
動作確認環境 Windows7 Excel: 2007, 2013 説明 1つのセルをア
-
-
実行中のマクロを中断する – Excel VBA
動作確認環境 Windows7 Excel: 2007 手順 Ctrl + Pause
-
-
[Excel VBA] 印刷時の余白、ヘッダー、フッター等を設定する。
動作確認環境 Windows7 Excel: 2007、2013 説明 余白、ヘッダー、
-
-
[Excal VBA] セルやセル範囲を選択する – Range.Select メソッド-
動作確認環境 Windows7 Excel: 2007 説明 セルやセル範囲を選択します