ExcelVBAのChangeイベント

ワークシートに対して※特定の操作が行われた時に実行されるのがイベントプロシージャです。今回はその中のChangeイベントについてエクセルの神髄のVBA100本ノック54本目を題材にしてご紹介します。

※特定の操作とは、セルが変更された時、セルをダブルクリックした時、セルを右クリックした時などです。

また、イベントプロシージャに必須のApplication.EnableEvents = Falseについても詳しく解説します。

イベントプロシージャを使用する方法

イベントプロシージャを使用する場合、標準モジュールに作成するのではなく、イベントを発生させたいオブジェクトモジュール内にプロシージャを作成します。

①仮にSheet1内で特定操作が行われた時に実行されるイベントプロシージャを作成する場合は、VBEのプロジェクトエクスプローラー内のSheet1を選択します。

左側のプルダウンからWorkSheetを選択、右側のプルダウンメニューから使いたいイベントの種類を選びます。今回はChangeを選択します。

すると、下記のようなChangeイベントプロシージャが自動的に表示されますので、プロシージャ内にコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

主なワークシートイベント

VBA100本ノック54本目

イベントを使い、商品コードを入れたら「マスタ」から取得した商品名と単価が直ちに表示されるようにしてください。
商品コードがマスタに存在しない場合は商品コードを赤字にしてください。
※金額はセルに計算式が入っています。
※商品コード列の既定の文字色は「自動」

ダウンロードデータはエクセルの神髄VBA100本ノック54本目より

「伝票」シート、「マスタ」シートの２シートあります。

下図は「伝票」シート

下図は「マスタ」シート

模範解答

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    
    On Error Resume Next
    Application.EnableEvents = False
    
    Dim mst As Range
    Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1)　'「マスタ」シートの1列目を変数mustに代入
    
    Dim rng As Range, i As Long
    For Each rng In Target
        rng.Font.ColorIndex = xlAutomatic　'フォントの色を自動に設定
        rng.Offset(, 1) = ""　　　　　　　　  '変更したセルの１つ右隣セルを空白
        rng.Offset(, 3) = ""　　　　　　　　  '変更したセルの３つ右隣セルを空白
        If Not IsEmpty(rng) Then               '変数rngが空白でなければ
            Err.Clear
            i = WorksheetFunction.Match(rng, mst, 0)
            If Err Then
                rng.Font.Color = vbRed
            Else
                rng.Offset(, 1) = mst.Cells(i, 2)
                rng.Offset(, 3) = mst.Cells(i, 3)
            End If
        End If
    Next
    
    Application.EnableEvents = True
End Sub

引数：Targetとは

コードを確認すると、Targetが頻出します。

If Target.Columns.Count > 1 Then Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub

イベントプロシージャにおけるTargetの役割は、変更されたセルが引数Targetに渡されます。

よって、上記コードは、以下のように訳されます。シートの中でイベントが発生するセル範囲を限定しています。

If Target.Columns.Count > 1 Then Exit Sub　'変更されたセルの列数が1以上ならば、マクロ終了
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub　'変更したセルが２列目以外ならば、マクロ終了
If Target.Row = 1 Then Exit Sub '変更セルの行が1行ならば（1行目の変更であれば）マクロ終了

つまり、イベントが実行されるセル範囲を赤枠部分に限定するコードです。

Intersectメソッド

Intersectメソッドは、複数セル範囲の重なっている部分のセル範囲を取得します。

重なる部分がない場合はNothingを返します。

Application.Intersect(Arg1(セル範囲1),Arg2(セル範囲2),Arg3(セル範囲3),・・・・）

※Arg・・・Ragneオブジェクト

Intersectメソッド（例）

Sub test()
    Debug.Print Intersect(Range("A1:E10"), Range("C1:H10")).Select
End Sub

セルA1からE10とセルC1からH10の重なる部分が選択されます。

Ｍach関数

Dim mst As Range
Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1)　'「マスタ」シートの1列目を変数mstに代入
i = WorksheetFunction.Match(rng, mst, 0)

Much関数は、指定した範囲内で探したいデータがどの位置にあるかを列・行番号で返す関数です。

=Mach（検査値,検査範囲,［照合の種類］）

［照合の種類］・・1＝以下、0＝完全一致、-1＝以上

よって、引数：Targetに入っている「検査値」を以下の検査範囲（「マスタ」シートの1列目）から探して、その行番号を変数ｉに代入しています。

Application.EnableEvents = False

イベントプロシージャ（今回はChangeイベント）は、セルに変更があった時にイベントが実行されます。

今回の場合は、「伝票」シートの「商品コード」フィールドのセルが変更された時にイベントが発生するわけです。

イベントが発生して、エラーの場合はフォントが赤くなる、または、「商品名」「単価」が入力される処理等が行われるわけですが、それらのセルの変更をきっかけにまたイベントが発生します。そしてまた、イベント発生でセルが変更されて、イベントが発生して・・・と無限ループとなります。

それを防ぐために、セルの変更が行われるコードの前にApplication.EnableEvents = Falseを設定しておくことでイベントは一旦停止されます。

つまり今回の場合であれば、フォントが赤くなる（セルの変更）処理についてはイベントは発生しないということになります。

そして、Application.EnableEvents = Trueとすることで、イベントの停止を解除しています。

  Application.EnableEvents = False
　Dim mst As Range
    Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1)
    
    Dim rng As Range, i As Long
    For Each rng In Target
        rng.Font.ColorIndex = xlAutomatic
        rng.Offset(, 1) = ""
        rng.Offset(, 3) = ""
        If Not IsEmpty(rng) Then
            Err.Clear
            i = WorksheetFunction.Match(rng, mst, 0)
            If Err Then
                rng.Font.Color = vbRed
            Else
                rng.Offset(, 1) = mst.Cells(i, 2)
                rng.Offset(, 3) = mst.Cells(i, 3)
            End If
        End If
    Next
　　Application.EnableEvents = True

まとめ

今回はイベントプロシージャのChangeイベント、そして「Target」と「Application.EnableEvents = False」をVBA100本ノック54本目を題材にして紹介しました。

エクセル関連の記事を多く掲載していますので、次回もお楽しみに！！

• FileSystemObjectで自動連番付与設定
• フォルダ自動作成とファイル保存（コピーを保存）
• ユーザーフォームを使ったレコード抽出方法－基本－
• ユーザーフォームとコンボボックスの値の取得
• VBAユーザーフォームとコンボボックス