[[20140308142117]]　『worksheet_changeで実行と実行しない場合分け』（ナッツ）　ページの最後に飛ぶ

『worksheet_changeで実行と実行しない場合分け』（ナッツ）

Worksheet_changeイベント内に次のようにfor文が複数あり､
セルE8の内容が特定の値（001など）の場合だけ
Range("A1:B65536").ClearContents
といった処理を実行させたくないのです。
しかし、どこに記述すればいいのかわからず、うまくいきません｡

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address
    Case "$E$8"

    If Range("E8").Value = "001" Then
    '何もしない
    Else
        Range("A1:B65536").ClearContents
    End If

            For j = 1 To Worksheets("シート").Cells(Rows.Count, "E").End(xlUp).Row
                If 条件1 Then
                    実行1
                End If
            Next j

            For i = 1 To Worksheets("シート").Cells(Rows.Count, "E").End(xlUp).Row

                If 条件2 Then
                    実行2
                End If
            Next i
End Select
End Sub

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 最近同じ事を書いた気がするのですが、値変更の処理の前後は
 Application.EnableEvents = False
 Application.EnableEvents = True
 で囲んでください。

 でないと、処理中の変更が Worksheet_Change イベントを発生して、ループに
 なります。

 この意味をわからないままでいると、同じ事を繰り返してしまうので、内容を
 把握されたほうが良いと思います。

 で、肝心な質問のほうですけれど、ご自身で書かれた通りでよいと思いますが。

     If Range("E8").Value = "001" Then
    '何もしない
    Else
        Range("A1:B65536").ClearContents
    End If

 は
    If Range("E8").Value <> "001" Then
        Range("A1:B65536").ClearContents
    End If
 ともかけますので、条件の書き方を覚えると幅が広がります。

 ただ、E8 が文字列ではなく数値なら
    If Range("E8").Value <> "001" Then
 は
    If Range("E8").Value <> 1 Then
 としてください。
（Mook） 2014/03/08(土) 14:45

同じことを書かせてすみません。
コードを記述しても、うまくいかなかったのですが、
一度ブックを閉じてまた開いたら、うまくいきました。

（ナッツ） 2014/03/08(土) 15:36

 >同じことを書かせてすみません。 
 咎めているわけではなく、ここを理解しておかないと Worksheet_Change イベント処理は
 思うようにならないので、使用するための最低限の基礎知識とお考えください。

 >一度ブックを閉じてまた開いたら、うまくいきました。
 がどうしてかはわかりませんが、うまくいったようで何よりです。
（Mook） 2014/03/08(土) 22:31

Application.EnableEvents = False
Range("A1:B65536").ClearContents
Application.EnableEvents = True
とすればよいのでしょうか。まだ理解できていません。
select case でE8セルを指定しているので、
E8以外のセルが変更されてもループは発生しないのではないか、
と思っていました。

『Worksheet_ChangeイベントとVLookup』の質問でご指摘後に動作した部分も、
Range("D12").Value = Application.WorksheetFunction.VLookup(Target.Value & "*", Worksheets("得意先台帳").Range("C:R"), 15, False)
の後にいろいろ追加記述したら（VLoopupやfor~next文など）、
エラーが出るようになり
（『エクセルでサジェスト機能』（ヒロ）質問内のコードをコピペしているのですが、
その Tg.Value = v(1) のところ）、
これもApplication.EnableEventsの記述場所がおかしいのかもしれません。

さらにブック閉じて開きなおす度に入力規則が削除されてしまい、、
スタック領域不足やらメモリ不足やらも出るようになり、
excel自体が頻繁に再起動するようになり、休日から涙目になり、
やはり素人が手を出すべきではない、と挫けそうです。

（ナッツ） 2014/03/09(日) 11:38

 上記をお呪いと思っていると、制限すべきところを制限できず、解除すべきところで
 解除できません。

 Worksheet_Change はセルの「値」が変更されたときに、この処理が行われるという
 「イベント」処理です。
 その中で Range("E8").Value や Target.Offset(0,1).Value などセルの値を変更すると
 それがきっかけになって、処理の途中で Worksheet_Change が始まってしまいます。

 ですから、今から変更するのは処理しないようにということで
 Application.EnableEvents = False 
 て変更して、セルの値の変更が終わったら、またイベントが発生するように
 Application.EnableEvents = True
 に戻します。
 これを戻さないと、通常の操作でセルの値を変えてもイベントが発生しません。
 (EXCEL の再起動で初期状態は有効なので戻りますが)

 Tg.Value = v(1) もセルの値の変更ですから、前後で制御は必要です。
 でも戻し漏れがないのであれば、処理の初めの方で無効にして、終わりの方で有効にする
 など全体を囲っても良いです（途中に Exit Sub がなければ）。

 >スタック領域不足やらメモリ不足やらも出るようになり、 
 というのは、やはり延々とイベントが発生しているのが理由のような気がします。

 追加した部分のコードを提示して質問してはどうでしょうか。
（Mook） 2014/03/09(日) 12:21

落ち着いてコードを見直し、Tg.Value = v(1)のある部分をはじめ、
怪しいところをApplication.EnableEventsで制御するようにしました。
すると、スタック領域不足、メモリ不足、ループする感じ
（Application.ScreenUpdating = Falseにしてもマウスポインタが高速点滅）
がなくなりました。

VLookupのエラーは、検索値の、
Target.Value & "*"　　　　　を
"*" & Target.Value & "*"
に変更したら解決しました。
これまでたまたま検索値が前方一致で済んでいたのに気が付きませんでした。

入力規則は削除されると悲しいので、
これからはシートからでなく、VBAで指定しようと思います。

今日本屋でVBA本を立ち読みしていたのですが、
基本は説明されればいくらか理解できても、
実践しようとしても応用がきかないという自分の非力を痛感しました。

ともかくMookさまのおかげで、前に進めそうです。
ご指摘、ご教授、本当にありがとうございました。
（ナッツ） 2014/03/09(日) 20:21

コメント返信：

[ 一覧(最新更新順) ]

[[20140308142117]] 『worksheet_changeで実行と実行しない場合分け』（ナッツ） ページの最後に飛ぶ

[[20140308142117]]　『worksheet_changeで実行と実行しない場合分け』（ナッツ）　ページの最後に飛ぶ