エクセル：シート内に入力があった場合のマクロの書き方

質問

シート内に入力があった時の処理を作作りたいです。

現状の作り方は、シートのコードにWorksheet_Changeを用意し、
その中に「Target.Addressを取得し、そのアドレスに応じて処理をする」と書き込んでいます。
例えば以下は、入力があったら３列離れた同じ行に同じ内容入力をする、というコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim T_row As Integer, T_col As Integer

　　T_row = Target.Row
　　T_col = Target.Column + 3

　　Application.EnableEvents = False

　　Cells(T_row, T_col).Value = Target

　　Application.EnableEvents = True
End Sub

このコードの作り方だと、１つのセルへの入力にしか対応しないというか、
ドラッグやコピー＆ペースト等、複数のセルにまたがった操作をすると動きません。
そういった通常可能な操作にも対応できるように作っていきたいのですが、
その場合どのように書き始めていけばよいでしょうか。

面倒な質問となってしまいましたが、
お時間あればよろしくお願いします。

回答No.1

KenKen_SP · ベストアンサー

> ドラッグやコピー＆ペースト等、複数のセルにまたがった操作を
> すると動きません。

対処方法には、下記のような方法（一案）があります。

■1.　ドラッグまたは、コピー＆ペースト処理自体を無効化する
　　　コードは困難で、かつユーザーの操作性を低下させることになる。
　　　不満の声がきっとあがります。お勧めできません。

■2.　複数のセルにまたがった場合、左角ひとつのセルのみを処理対象とする
　　　コードは割と簡単な修正で済みます。割り切ることも時に必要です。
　　　複数セルの Target の内、左角のセルは Target(1).Value とすれば値は
　　　とりだせます。

　　　　　Cells(T_row, T_col).Value = Target(1).Value

■3.　For Each ループで Target のセル毎に分割処理する
　　　コードはやや難しくなりますが、一番細かく場合分けが可能な方法。
　　　転記条件が複雑な場合に向きます。

　　　Dim r as Range
　　　For Each r In Target.Cells
　　　　　Cells(r.Row, r.Columns+3).Value = r.Value
　　　Next

■4.　Resize 転記先セル範囲を Target と同サイズにし、一括転記する
　　　コードは簡易ですが、理屈の理解は難解かも。
　　　セルの値で条件分岐するような処理はできません。
　　　また、転記元・先に結合セルがあると厄介です。

Private Sub Worksheet_Change(ByVal Target As Range)

　　Dim r　As Long, c　As Long　' // Row Nume & Col Num
　　Dim rc As Long, cc As Long　' // Rows Count & Cols Count

　　With Target
　　　　r = .Row
　　　　c = .Column + 3
　　　　rc = .Rows.Count
　　　　cc = .Columns.Count
　　End With

　　Application.EnableEvents = False
　　' // 左角セルから Resize で範囲を広げて一括転記
　　Cells(r, c).Resize(rc, cc).Value = Target.Value
　　Application.EnableEvents = True

End Sub

補足

回答ありがとうございます。

3.の方法が自分のやりたいものに対し向いてそうです。
今後作るコードにも当てはめやすそうだと感じました。
以前も似たようなことを質問し、似たようなコードで回答を頂いたのですが、
For Each r In Target.Cellsで全セルに対して地道に処理をしていたのですね。
今唐突に理解できました。
何でForがあるのかとか思ってました。

最初の質問からは離れるのですが、
提示していただいた3.のコードの
Cells(r.Row, r.Columns+3).Value = r.Value
はなぜApplication.EnableEvents = Falseが要らないのでしょうか。
自分の作ったコード↓だと、
「Cells(T_row, T_col).Value = Target」を行うたびに同じコードが実行されて３列置きに入力を繰り返し、
256列を超えたところでエラーが出て止まるため、
Application.EnableEvents = Falseをつけて１回しか実行しないようにしたのですが。

回答No.3

KenKen_SP

> 自分の想定では別プログラムが動作するはずはない、という時でも
> 一応入れておくべきだ、ってことでしょうか。

そうですね。

Change イベント内でセルの値を更新した場合、Application.EnableEvents
が記述されていなくても処理内容によっては問題なく動くことがあります。
しかし、これは飽くまで「見かけ」の話で、イベントは多重発生しているはずです。

まあ、ちょっとした手間ではありますけど下記の押さえになりますので、
入れておいた方が良いと思うのです。私見ですけども。

　・重大なエラー発生の可能性
　・CPU パワー等の不必要な消費

# 遅ればせながら、Columns + 3 は Column + 3 の誤りでした。
# 直打ちで確認もせず投稿してしまい、コードのミスに気がつきませんでした。
# すみません。

お礼

Application.EnableEventsは入れるようにしようと思います。

本題の方も、
For Each r In Target.Cellsで今の所、思ったような動作を作れています。
（毎度のことながら、Rangeのイイ変数名が思いつかないので[ r ]という変数名までそのまま流用してますが）

色々ありがとうございました。

回答No.2

KenKen_SP

> なぜApplication.EnableEvents = Falseが要らないのでしょうか。

いや、ポイントだけ書いたので省略されてるだけです。
ご質問文のコードに Application.EnableEvents があったので、
その点はご存知なのだと思ってました。すみません。

Change イベント内でセルの値を更新する場合は、

　　Application.EnableEvents = False

を入れる必要がありますので、補完して下さい。

余談になりますが。。

これは、イベントの２重起動を予防してコードの「誤動作を防ぐ」
というのが第１の理由ですが、「不要プログラムは起動させない」
というマナーの問題でもあるように感じます。

お礼

>ポイントだけ書いたので省略されてるだけです。

いえ、実際に挙げていただいたコードを「Application.EnableEvents = False」無しでそのまま試してみたのですが、
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
For Each r In Target.Cells
Cells(r.Row, r.Columns + 3).Value = r.Value
Next
End Sub
このコードだと「Application.EnableEvents = False」が無くても
「Cells(r.Row, r.Columns + 3).Value = r.Value」が１回しか実行されませんでした。

自分のコード↓を似せたものをForの中に入れると256列に達するまでループして止まるのです。
T_row = r.Row
T_col = r.Column + 3
Cells(T_row, T_col).Value = r.Value

で、何でだろう、と質問したわけですが、
よくみたら、
挙げていただいたコードは「Column + 3」ではなく「Columns + 3」でした。
これのせいだったみたいです。
失礼しました。
再度回答させて申し訳ありません。

>マナーの問題でもあるように感じます。
自分の想定では別プログラムが動作するはずはない、という時でも一応入れておくべきだ、
ってことでしょうか。
人間、よく忘れますから「想定」なんてアテにならないですし。