変更イベント時に、セルに色を塗るマクロ

Question

セルC20の変更イベント時に、セルF1を黄色に塗るマクロを作成しています（下記）

1回目に、セルC20に変更を加えた際は、セルF1の無事塗りつぶしできたのですが、
一度セルF1の色をクリアにし、
2回目に、セルC20に変更を加えると、セルF1の塗りつぶしはされず、何も起こりません

調べると、1回目の処理後、Application.EnableEvents = Falseになったまま
C20変更のイベント発生が発生していないようです。

そのため、イベント認識するように、
下記のように■に
Application.EnableEvents = Trueを記述しましたが、
どうしても変更イベントが発生しません

修正すべき点を教えて頂けないでしょうか。

Private Sub Worksheet_Change(ByVal Target As Range)

    	Application.EnableEvents = True ■

    If Target = Range("C20") Then
        Application.EnableEvents = True  ■
    
        Range("F1").Interior.Color = RGB(255, 255, 0)

        Application.EnableEvents = False
    End If
    
    If Application.EnableEvents = False Then
        Debug.Print "False"
    Else
        Debug.Print "True"
    End If
    
    
End Sub

Answer 1

最後にApplication.EnableEvents = Falseをしているため、
それ以降Worksheet_Changeも反応しなくなっています。

そのため頭にApplication.EnableEvents = Trueを入れても反応しません。
※そもそもWorksheet_Changeが動かないのでTrueにならない

いったんイミディエイトウィンドウででも
Application.EnableEvents = Trueを実行すればそれ以降は動きますが、
このままではまた動かなくなります。

Application.EnableEventsをすべて削除すれば次回からは普通に動きます。
別の場所でApplication.EnableEvents=Falseで終わるものがあるなら、
Application.EnableEvents=Trueで終わるように変更してください。
－－－－－
他にもWorksheet_Changeがあったりすると影響を受けて無限ループになるので、
その間だけFalseにして、最後にTrueに戻します。

Private Sub Worksheet_Change(ByVal Target As Range)

        Application.EnableEvents = False

    If Target = Range("C20") Then

        Range("F1").Interior.Color = RGB(255, 255, 0)

    End If

    If Application.EnableEvents = False Then
        Debug.Print "False"
    Else
        Debug.Print "True"
    End If

        Application.EnableEvents = True

End Sub

Answer 2

サンプルを拝見しました・・。

素人考えですが Application.EnableEvents = Falseなどはメゾットなので

if　thenで判定できないのではないでしょうか・・・。

その証拠にif elseでmsgbox（debug.printも反応しない）を入れて判定しても反応しないためです。

そもそも特定のセルのchangイベントを判定してF１の色分けをおこなっているのですから

イベント自体をON・OFFする意味がわかりませんが・・。

また、各イベントは、通常処理で影響しないようにTrueにして戻して終わるのがセオリーのような気がします。

（処理高速化でOFFにする場合もありますが、特定のセルのイベントを制御する処理ではあまり意味がないようなきがします）

下記のようなコードを実行前後に入れることはよくあります。

Sub マクロ開始()
Application.ScreenUpdating = False　　　　　　 '画面描画を停止
Application.EnableEvents = False　　　　　　　　'イベントを抑止
Application.DisplayAlerts = False　　　　　　 '確認メッセージを抑止
Application.Calculation = xlCalculationManual '計算を手動に
End Sub

Sub マクロ終了()
Application.StatusBar = False　　　　　　　　　　'ステータスバーを消す
Application.Calculation = xlCalculationAutomatic '計算を自動に
Application.DisplayAlerts = True　　　　　　　　'確認メッセージを開始
Application.EnableEvents = True　　　　　　　　 'イベントを開始
Application.ScreenUpdating = True　　　　　　 '画面描画を開始
End Sub

Comments

[koyamashinji]

ご回答頂き、誠にありがとうございます。
イベント自体をON・OFFするのは、WEB上で検索した際、無限ループに陥る可能性があると記載があったので、記述した次第です。
イベント自体をON・OFFが必要な場合とは、どのようなケースが想定されますでしょうか。
素人で恐縮です。

[mako1972]

確かにworksheetのイベントごとに判定するのはコストが高いと思います。
データが転記されるために、F1を塗りつぶすかどうかの判定が入りますので
worksheetのイベントを使うのが妥当かは精査が必要だとは思います。
（ボタンを押したタイミングで判定するなど・・・）

イベント自体をON・OFFする目的は。マクロ（VBA）を高速化するためです。
エクセルだと関数が埋め込まれている場合が大部分ですので、
データの取り込みなどの処理をするとマクロと同時に自動計算して
遅くなることがあります。そこで、エクセルの一般機能をOFFにして
マクロ優先で処理することでかなりマクロを高速化できます。

VBAを組む場合の定番中の定番ですので、重いExcelをいじっているうちにいずれわかると思います。
処理速度でいうと処理内容にもよりますが数倍ちがいます。
今回のサンプルですとほとんどというか、変わらないので入れる必要はないと思います。

これらのOFFにした処理はTrueで戻すのが鉄則です。
自分のエクセルならいいのですが、配布したエクセルだと相手のExcel一般機能がOFFのままに
なる懸念がありますのでお作法だと思った方がよろしいでしょう。

つたない説明ですが・・・。よろしくお願いいたします。

言いたいのは、OFFにしたらTrueが鉄則だということですね。

[koyamashinji]

ご丁寧に回答いただき誠に有難うございます。
非常に参考になりました。これからも精進いたします.

[mako1972]

Application.StatusBar = False　　　　　　　　　　'ステータスバーを消す

ここはtrueでした。

[imihito]

横から失礼します。回答の内容で認識が誤っていると思われる箇所があったため、コメントさせて頂きました。

> Application.EnableEvents = Falseなどはメゾットなので

`EnableEvents`は一般的には「メソッド」ではなく「プロパティ」と呼ばれるもので、`EnableEvents`の場合、値の取得と設定が可能です。

値の取得が可能ということは、`Application.EnableEvents = False`は`True = False`または`False = False`と評価できる式となるため、If の条件として使用できます。

[mako1972]

プロパティですか。勉強になります。

Answer 3

ChangeイベントでApplication.EnableEvents = Falseをしてイベント処理を終了しているので、以降のイベントがすべて発生しなくなってしまいます。

Application.EnableEvents = Falseにする必要は無いと思いますが、なにか理由がありますか？