VBA｜特定のセル範囲が更新されると起動するマクロ

Contents

エクセルのイベントで動作するマクロ

エクセルVBAには特定の操作がおこなわれたことをきっかけにして、マクロを動かす方法があります。これらはイベントプロシージャと呼ばれますが、多数の種類が存在しています。

こちらの記事では、それらのイベントプロシージャのなかから、特定のセルの範囲が更新されたことをきっかけにして動かすマクロのサンプルを紹介します。

なぜ、膨大な事務作業でも定時で退社できるのか。

残業はしたくない！PCやExcelのスキルアップであなたのプライベート時間を奪わせない！
実務をプロから学べる「ユースフル」の動画は永年見放題。Q&A機能で分からないを放置しないから安心。

詳しくは以下のリンクをクリック

30秒の簡単入力で無料体験

ワークブックのイベントの指定方法

この記事ではイベントプロシージャで動作するマクロの機能を掲載します。イベントプロシージャの指定方法は以下の記事を参考にしてください。

ワークブックイベントの指定方法は以下のリンクをクリック

VBA｜イベントプロシージャ一覧(ワークブックイベント)

VBAには数多くのイベントプロシージャがあります。イベントプロシージャは特定の操作をきっかけにするマクロになります。適切なイベントを指定することでマクロを利用する人を補助し、作業の効率や正確さを上げる効果をもたらす可能性があります。こちらの記事ではそんなイベントプロシージャのワークブック

マクロを利用する入力用ワークシート

紹介するマクロで利用するサンプルデータが書かれたシートについては以下のとおりです。

サンプルデータシートの画像

サンプルデータシートの説明

B列に商品名、C列に単価、D列に在庫数が入力されており、F列に前回発注日を日時で入力されております。これらのセルはサンプルデータシートの使用者が編集し、更新がされる想定です。

E列とG列はこれらの値に応じて自動で計算されるようになっています。

入力用のセルと入力された値によって計算結果が表示されるシートだよ。

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

セル範囲をきっかけに動作するマクロをつくるために、以下のイベントプロシージャをつかっていきます。

指定するイベントプロシージャ

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

「Workbook.SheetChange」イベントは、ワークブック(エクセルファイル)に含まれるシートのセルが変更されたことをきっかけにするイベントプロシージャです。つまり複数のシートが存在すればそれらすべてのシートのセルの変更でイベントが発生します。

引数のShは変更したシート、Targetは変更したセル範囲が代入されるよ。

特定のセル範囲が更新されたら更新日時を書きかえるマクロ

サンプルデータシートが更新されたことをきっかけにして、G2セルに記入された更新日時の値を自動で更新するマクロのサンプルコードは以下のとおりです。

サンプルマクロのコード

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '特定のセル範囲を代入する変数
    Dim Target_Range As Range

    'B4:G13のセル範囲を代入する変数
    Set Target_Range = Worksheets("サンプルデータ").Range("B4:G13")

    '対象シートが更新されたかの判定式
    If Sh.Name = "サンプルデータ" Then

        '代入したセル範囲が更新されたかの判定式
        If Intersect(Target, Target_Range) Is Nothing Then
           '特定のセルの範囲以外の処理があればここに書く。

        Else
            With ThisWorkbook.Worksheets("サンプルデータ")
                '更新日時が記入されたG2セルを現在の日時に更新する
                .Range("G2") = Format(Now(), "yyyy/mm/dd hh:mm")
            End With
        End If
    End If
End Sub

サンプルマクロの解説

変数「Target_Range」にサンプルデータシートのB4からG13セルの範囲を代入しています。

そのあとで分岐の条件に「Target_Range」に代入された範囲が更新された場合と、それ以外のセルが更新された場合で処理をわけています。具体的な方法としては、Intersectメソッドを使って条件を指定しています。

Intersectメソッドとは

Intersectメソッドは、引数に指定したセル範囲の重なる範囲を返します。例えば、A1からB2のセル範囲と、B1からC2までのセル範囲を引数に指定して実行した場合は「$B$1:$B$2」が返されます。

イメージしにくい人は以下の画像をご覧になってください。

Intersectメソッドの効果イメージ

※Debug.Print Intersect(範囲1, 範囲2).Addressを実行した場合、$B$1:$B$2が返ってきます。

また、変数に代入されたセル範囲を比較して重ならない場合はNothingを返します。

サンプルマクロでは、このIntersectメソッドを利用してサンプルデータシートの使用者に更新されたセル範囲が「Target」に代入され、「Target_Range」のセル範囲と比較して重なった範囲が見つかれば、更新日時を書きかえるための分岐に入ります。

つまり、サンプルデータシートのB4からG14セルの範囲が更新されると、G2セルの更新日時を現在の日時に更新します。(B4からG14セル以外が更新されてもG2セルの更新日時は書きかえません。)

直近でサンプルデータのシートを更新した日時が自動で入力されるね。

更新日時を書きかえる処理に表示形式を指定するためにFormat関数を使用しています。

Format関数ついてはこちらで詳しく解説していますので以下のリンクよりご覧ください。

VBA｜文字列を処理するときに使える文字列関数21選

エクセルVBAには、ワークシート関数と同じように多数の関数が用意されています。関数の利用は、効率よくマクロを作るうえで有効な方法です。関数は目的によって種別がわかれますが、ここでは文字列操作(検索・置換・抽出・挿入・削除)ができる関数の使い方や効果をコードのサンプルつきで紹介します。

特定のセル範囲が更新されたら自動で計算するマクロ

サンプルデータシートが更新されたことをきっかけにして、E列に記入された合計金額の値を自動で更新するマクロのサンプルコードは以下のとおりです。

サンプルマクロのコード

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '特定のセル範囲を代入する変数
    Dim Target_Range As Range

    'B4:D13のセル範囲を代入する変数
    Set Target_Range = Worksheets("サンプルデータ").Range("B4:D13")

    '対象シートが更新されたかの判定式
    If Sh.Name = "サンプルデータ" Then

        '代入したセル範囲が更新されたかの判定式
        If Intersect(Target, Target_Range) Is Nothing Then
　　　　　'特定のセルの範囲以外の処理があればここに書く。

        Else
            With ThisWorkbook.Worksheets("サンプルデータ")
                Dim i As Long: i = Target.Row '編集された行を変数に代入する
                .Cells(i, 5) = .Cells(i, 3) * .Cells(i, 4) '合計金額の入力
            End With
        End If
    End If
End Sub

サンプルマクロの解説

変数「Target_Range」にサンプルデータシートのB4からD13セルの範囲を代入しています。つまりサンプルデータシートの商品名・単価（円）・在庫数が書かれた列が代入されます。

更新されたセル範囲の判定式については、更新日時を書きかえるマクロと同じくIntersectメソッドを使用しています。これにより商品名・単価（円）・在庫数が更新されると分岐に入り、そのあとの処理が実行されます。

つぎに、合計金額（円）が記入されたE列の計算を実行する処理ですが、使用者によってサンプルデータシートが更新された行のE列を再計算したいので、変数「Target」から行数を取得するRowプロパティを使っています。

さいごに取得した行数を変数「i」に代入して計算結果を記入します。

使用者が値を書きかえた行数を利用するところがポイントだよ。

特定のセル範囲の編集を禁止するマクロ

サンプルデータシートのE列やG列は自動計算の結果を出力するセルであるため、使用者による更新や削除を禁止したい範囲となります。マクロのサンプルコードは以下のとおりです。

サンプルマクロのコード

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '特定のセル範囲を代入する変数
    Dim Target_Range As Range

    'E4:E13とG4:G13セル範囲を代入する変数
    Set Target_Range = Worksheets("サンプルデータ").Range("E4:E13,G4:G13")

    '対象シートが更新されたかの判定式
    If Sh.Name = "サンプルデータ" Then
        
        '代入したセル範囲が更新されたかの判定式
        If Intersect(Target, Target_Range) Is Nothing Then
           '特定のセルの範囲以外の処理があればここに書く。

        Else
            Application.EnableEvents = False 'イベントを無効にする
            MsgBox "編集したセルは変更できません。", vbOKOnly + vbCritical, "不正操作を検知"
            Application.Undo '直前の処理に戻す
            Application.EnableEvents = True 'イベントを有効にする
        End If
    End If
End Sub

サンプルマクロの解説

変数「Target_Range」にサンプルデータシートのE4からE13セルとG4からG13セルの範囲を代入しています。つまりサンプルデータシートのE列の合計金額（円）・G列の発注フラグが書かれた列が代入されます。なお、これらの列は数式や関数で結果を出力していますので、使用者から書きかえられたくない範囲です。

続いて、これまでのサンプルマクロでもご紹介したIntersectメソッドでの条件判定です。これにより使用者が更新したセルがE列やG列であった場合は分岐に入ります。

そしてセルの編集を禁止する具体的な手段として「Application.Undo」を利用しています。「Application.Undo」は直前の状態に戻す処理ができます。

ここで注意しておきたいのは、直前の状態に戻す処理についてもセルの編集とみなされるため「Application.Undo」で直前の状態を戻すと、これをきっかけにあらたにマクロを動かすイベントが起動してしまい、無限にループが発生します。

上記のループ動作を防止するため、編集された場所が特定のセル範囲であった場合の条件分岐のなかで一度イベントを無効にする処理「Application.EnableEvents = False」を実行しています。

さいごに「Application.EnableEvents = True」で無効になったイベントを有効に戻しています。この設定値を戻すことを忘れると、初回のみ起動するイベントプロシージャになってしまうので注意が必要です。