excel2021で実行できないマクロ。どこを直したらいいのか

下記マクロだけexcel2021で実行できないのですが、どこを直せば動くでしょうか。。
実行するとちょっと固まってexcelの画面ごと閉じてしまいます。
excel2011では動きます。
他のマクロも入ってるファイルですが、このA列に指定したマクロだけ動きません。

・A列は、ルールに沿った番号以外は入力出来ないようにしている（実行できない）
他の列のセルに計算式が入っています。
D列「=LEFT(A1,10)」
E列「=IFERROR(D1*10+(MOD(D1,7)),"")」

・B列は指定の番号を入力すると指定した文字が入力される（これは実行できる）

---------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Intersect(Target, Range("A1:A245")) Is Nothing = False Then

If Val(Worksheets("sheet1").Range("A" & Target.Row).Value) = Val(Worksheets("sheet1").Range("E" & Target.Row).Value) Then

Worksheets("sheet1").Range("A" & Target.Row).Value = "'" & Worksheets("sheet1").Range("A" & Target.Row).Value

Else

MsgBox "番号が違います"

Worksheets("sheet1").Range("A" & Target.Row).Value = "再入力"

End If

ElseIf Intersect(Target, Range("B1:B245")) Is Nothing = False Then

If Worksheets("sheet1").Range("B" & Target.Row).Value = "1" Then

Worksheets("sheet1").Range("B" & Target.Row).Value = "済"

With Worksheets("sheet1").Range("B" & Target.Row)

End With

ElseIf Worksheets("sheet1").Range("B" & Target.Row).Value = "2" Then

Worksheets("sheet1").Range("B" & Target.Row).Value = "未確認"

With Worksheets("sheet1").Range("B" & Target.Row)

End With
　　 End If
　　 Else
　Exit Sub
End If

End Sub

---------------------------------------------------------------------------

分かる方いらっしゃいましたら、教えて頂けましたら幸いです。

No.1 ベストアンサー 回答者： よろずやkinchan 回答日時： 2022/03/28 07:01

イベント処理の基本が全く出来ていない。

これまで動いていたのが奇跡。

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A245")) Is Nothing = False Then
        If Val(Target.Value) = Val(Range("E" & Target.Row).Value) Then
            Application.EnableEvents = False
            Target.Value = "'" & Target.Value
            Application.EnableEvents = True
        Else
            MsgBox "番号が違います"
            Application.EnableEvents = False
            Target.Value = "再入力"
            Application.EnableEvents = True
        End If
    ElseIf Intersect(Target, Range("B1:B245")) Is Nothing = False Then
        If Target.Value = "1" Then
            Application.EnableEvents = False
            Target.Value = "済"
            Application.EnableEvents = True
        ElseIf Target.Value = "2" Then
            Application.EnableEvents = False
            Target.Value = "未確認"
            Application.EnableEvents = True
        End If
    End If
End Sub

この回答へのお礼

遅くなりました。
お陰様で完璧に動くようになりました。
ありがとうございます！

お礼日時：2022/04/14 22:07

No.2 回答者： Qchan1962 回答日時： 2022/03/31 01:45

こんばんは

＞どこを直せば動くでしょうか
これは、すでにコードを示されていますので参考にされるのが良いと思います。もっとも、On Error Resume Nextがあるので、実行の初め
On Error Resume Nextの一行下に　Application.EnableEvents = False
とプロシージャを抜ける可能性のある　Exit Sub　と　End Subの一行上に
Application.EnableEvents = True　を記せば良いと思います。

＞このA列に指定したマクロだけ動きません。
A列の変更でA列の変更を行うイベントプロシージャだからです。
つまり、
セルが変更された＞＞下記を実行
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Range("A1:A245")内が変更された場合、実行条件が成立
If Intersect(Target, Range("A1:A245")) Is Nothing = False Then

Range("A" & Target.Row).Value = "'" & Wo・・・　又は
Range("A" & Target.Row).Value = "再入力"　で
セルが変更されたので
Private Sub Worksheet_Change(ByVal Target As Range)が実行される

Range("A" & Target.Row)はトリガーとなったセルと同じなので実行条件が成立
また、変更され・・Private Sub Worksheet_Change(ByVal Target As Range)が実行される

同じ処理が永遠に繰り返されます。したがってメモリスタックして
＞ちょっと固まってexcelの画面ごと閉じてしまいます。
となります。

ちなみにすべての書き込み先がRange("A" &　出なくRange("B" &　ならば
If Intersect(Target, Range("A1:A245")) Is Nothing = False Thenの
実行条件に当たらないので　Exit Sub　または　End Sub　で終了します
しかし、すべてがRange("A" &　以外であっても
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("A1:A245")) Is Nothing = False Then
は、実行されます。（はじめとVBA処理で書き込みを行う回数）

これは、自身でデバッグすると解ります。
If Intersect(Target, Range("A1:A245")) Is Nothing = False Thenに
ブレイクポイントを設定してA1セルに何か入力します。
VBAの実行はブレークポイントで止まります。
止まっているのを確認しながら、ステップ実行で進めていくと
同じコードを何度も繰り返し処理をして先に進まない事が分かります。

デバッグについては、VBAデバッグ方法などで調べてください。

ちょっとのつもりが長文になってしまいましたが、
イベントをトリガーにした処理の場合、イベント発生を抑止するコード　
Application.EnableEvents = False　が必須となります。

厳密には、イベントプロシージャのみで良いと言う事では無く、
イベントをトリガーとして使っているブックを操作する場合は、考慮する必要があり、他のプロシージャでも念のため書いて置くとか私はします。
（三種の神器のように別プロシージャでON/OFFしています）
普通のプロシージャの回答などには、ほぼ見かけませんが、VBA処理をいろいろ書いたら処理が遅くなった。などの原因になる可能性もあります

Application.EnableEvents = False　のままだとイベントが発生しないので
処理の後は必ず
Application.EnableEvents = True　で戻します

デバッグなどでApplication.EnableEvents = Trueを実行出来なかった場合は、イミディエイトウィンドウで実行するか、プロシージャを作成して実行するか、ブックの再起動になります。閉じる時は保存を忘れずに・・

この回答へのお礼

遅くなりました。
詳しく説明していただき、ありがとうございます！
無事、動くようになりました。
勉強になります。勉強します。

お礼日時：2022/04/14 22:07