「ブロック機能」のリニューアルについて

下記マクロだけ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

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

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

教えて!goo グレード

A 回答 (2件)

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


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

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
    • good
    • 0
この回答へのお礼

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

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

こんばんは


>どこを直せば動くでしょうか
これは、すでにコードを示されていますので参考にされるのが良いと思います。もっとも、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を実行出来なかった場合は、イミディエイトウィンドウで実行するか、プロシージャを作成して実行するか、ブックの再起動になります。閉じる時は保存を忘れずに・・
    • good
    • 0
この回答へのお礼

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

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング