[[20200603105407]]　『入力文字に応じたChangeイベントで複数のセルを変』（くくり）　ページの最後に飛ぶ

『入力文字に応じたChangeイベントで複数のセルを変更した時にエラーが起きる』（くくり）

いつもお世話になってます。

Excel表で判定列に入力した内容に応じて隣のセルに罫線で下線を引くように色々調べてChangeイベントを書いてみたのですが
複数セルにコピペ等で同じ文字を複数セルに入力したり複数セルの内容を消すとエラーになってしまいます。
複数変更してもそれぞれ処理するようになると良いのですが知識不足の為いい方法が見つからず
お知恵を貸していただきたいです。

現時点では以下のようにしています。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim kk As Range
Application.EnableEvents = False
Set kk = Intersect(Columns("A"), Target)
If Not kk Is Nothing Then

''''''承認が入力されたら太線

    If kk.Value = "承認" Then
    Target.HorizontalAlignment = xlRight
    Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlMedium
    End If
Application.EnableEvents = True

''''''保留が入力されたら二重線

    If kk.Value = "保留" Then
    Target.HorizontalAlignment = xlRight
    Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).LineStyle = xlDouble

    End If
Application.EnableEvents = True

''''''判定削除で普通の罫線に戻す

    If kk.Value = "" Then
    Target.HorizontalAlignment = xlGeneral
    Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlThin
    End If
Application.EnableEvents = True

End If

End Sub

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 こんな風にしてみてはどうでしょうか。

 If Not kk Is Nothing Then
     For Each e In kk
         '承認が入力されたら太線
         If e.Value = "承認" Then
             e.HorizontalAlignment = xlRight
             e.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlMedium
         End If
         （以下省略）

 なお、Application.EnableEvents = True　は
 一度だけでよいです。

 今のままだと、kk Is Nothing の場合、
 Application.EnableEvents = True
 は実行されないことになって不都合が生じます。

 If Not kk Is Nothing Then
 から抜けたところで一回だけ実行すればよいのでは？
（γ） 2020/06/03(水) 11:27

γ様

ご回答頂きありがとうございます。
コード試したところ理想通りの動きをしました。
For Eachを使えば繰り返しの処理で変更した全てのセルに対応できるという事ですね？

Application.EnableEvents = Trueについて
＞If Not kk Is Nothing Thenから抜けたところで一回だけ実行

これは挙げたコードで言えばA列で無ければ、が抜けた所になるのでしょうか？
勉強不足でお恥ずかしいのですがそれがコードのどこの部分なのかが分かりません。

試しに全ての条件の後に実行してみたのですが合っているのでしょうか？
（くくり） 2020/06/03(水) 14:48

■１
横からですけど、ChangeイベントのTargetは複数セルになり得るということ理解したほうが良いと思います。
（解決の方法には気づかれてるようなので、なんとなくは理解されているようにも思いますが）

さらに、セルの値に応じて３パターン（なにも処理しないことも含めれば4パターン）に分岐していますよね？
このように１つの条件で複数に分岐するような場合には、IFで分岐させることも間違いではありませんが、Select Caseによる分岐も可能です。

このほか、今回のケースではセルの値は変更していません。
したがって、Changeイベントで値が変わったからChangeイベントが発生して....という連鎖反応は起きません。
なので、そもそもイベントを一時的に無効にする必要はないかもしれません。

■２
ふまえて、このような感じでもよいとおもいます。

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim kk As Range

        '▼値が変化したセルの中にA列のものが含まれているか判定
        If Intersect(Columns("A"), Target) Is Nothing Then
            '//含まれていなければ即終了
            Exit Sub
        Else
            '//含まれている場合は、その中からkkに一つずつ取り出して処理了
            For Each kk In Intersect(Columns("A"), Target)

                Select Case kk.Value
                    Case Is = "承認" '承認が入力されたら太線
                        kk.HorizontalAlignment = xlRight
                        kk.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlMedium

                    Case Is = "保留" '保留が入力されたら二重線
                        kk.HorizontalAlignment = xlRight
                        kk.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).LineStyle = xlDouble

                    Case Is = "" '判定削除で普通の罫線に戻す
                        kk.HorizontalAlignment = xlGeneral
                        kk.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlThin
                End Select
            Next kk
        End If

    End Sub

（もこな２） 2020/06/04(木) 07:44

 書式しか変更していないから、Application.EnableEvents の操作は不要というご指摘は
 まったくそのとおりです。コードは一部をちらっとしか読んでいなかったので、
 全く気づかなかったですね(汗）。

| これは挙げたコードで言えばA列で無ければ、が抜けた所になるのでしょうか？
| 勉強不足でお恥ずかしいのですがそれがコードのどこの部分なのかが分かりません。

 に関して。
 Application.EnableEvents の操作が仮に必要だったとして、
 （一般論として）回答しておきますね。

 まず、コードはきちんとインデントをつけるべきですね。
 こうなります(*)

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim kk As Range
     Application.EnableEvents = False
     Set kk = Intersect(Columns("A"), Target)
     If Not kk Is Nothing Then
         ''''''承認が入力されたら太線
         If kk.Value = "承認" Then
             Target.HorizontalAlignment = xlRight
             Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlMedium
         End If
         Application.EnableEvents = True
         ''''''保留が入力されたら二重線
         If kk.Value = "保留" Then
             Target.HorizontalAlignment = xlRight
             Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).LineStyle = xlDouble
         End If
         Application.EnableEvents = True
         ''''''判定削除で普通の罫線に戻す
         If kk.Value = "" Then
             Target.HorizontalAlignment = xlGeneral
             Target.Offset(, 1).Resize(, 3).Borders(xlEdgeBottom).Weight = xlThin
         End If
         Application.EnableEvents = True
     End If
 End Sub
 これを見ると、
 If Not kk Is Nothing Then　が成立せずに、直ぐに終了したら、どうなりますか？
 Application.EnableEvents は Falseのまま終了しますよね。
 それ以降はイベント処理が起動しませんよ、という指摘でした。

 （注*)ちなみに、私は整形ツールを回答用に使っているので、ワンクリックで済みます。
       負荷ゼロです。
（γ） 2020/06/04(木) 08:44

返信遅れて申し訳ありません！

もこな2様
Select Caseでも対応可能との事でコードありがとうございます
＞'▼値が変化したセルの中にA列のものが含まれているか判定
＞'//含まれていなければ即終了
＞'//含まれている場合は、その中からkkに一つずつ取り出して処理了
いまいち理解できていなかった部分への補足もありがとうございます
そして、仰る通り変化が無ければイベント無効の必要がありませんね
これも付けるのが普通なのかなと深く考えずに付けておりました
もっとコードの意味を考えながらやらなければいけませんね
勉強になりました

γ様
インデントを付けると一目瞭然ですね
質問の際に書いたコードでお察しだと思うのですが
これまでは自分でなんとなくでインデントを付けていました・・・
きちんと付ければどれがどこに対応しているかこんなに分かりやすくなるのですね
整形ツールがあるとの事で、勉強の為に導入してみようと思います

お二方共ご丁寧な指導ありがとうございます！

（くくり） 2020/06/05(金) 17:03

コメント返信：

[ 一覧(最新更新順) ]

[[20200603105407]] 『入力文字に応じたChangeイベントで複数のセルを変』（くくり） ページの最後に飛ぶ

[[20200603105407]]　『入力文字に応じたChangeイベントで複数のセルを変』（くくり）　ページの最後に飛ぶ