Worksheet.Change イベントを他のシートにも反映させる方法？

Question

Sheet1は、
Worksheet.Change イベントにより、A1セルの値が変わると、ある行の表示・非表示が変わるようにしています。

Sheet2とSheet3は、Sheet1をコピーしたもので、
同じくA1セルの値が変わると、連動して行の表示・非表示が変わるのですが、

3シートともA1セルの値は同一にしたいので、
Sheet1だけ値を変えれば、Sheet2、Sheet3にも自動で反映したいのです。
これは、「=Sheet1!A1」で実現できます。

しかしそうすると、
Worksheet.Change イベントはSheet1でしか働かないため、Sheet2、Sheet3の行の表示・非表示が連動しません。

Sheet1のA1を変更するだけで、Sheet2、Sheet3の行の表示・非表示まで連動させるにはどうしたら良いでしょうか？

WindFaller · Accepted Answer

#3の回答者です。

ちょっと元の質問項目だけでは、要領を得ませんでした。

ただ、サブルーチン化すればよいので、まるっきり違うということもないとは思いますが、なぜ、そのような内容で、イベント・ドリブン型なのか、私には良く理解していません。何か、

>セルA1が書類のタイプA、タイプB、タイプCを選択するプルダウンメニューになります。
プルダウン・メニューでしたら、入力規則の時に、Worksheet_Change イベントが成立しますが、私には、そもそもの出発点が違っているように思うのです。どうしても、Worksheet_Changeでしたいとか、いろんな思わくが別にあるのでしたら、以下は無視して結構です。ただ、他のイベント・ドリブン型コードとは競合することはありません。また、以下は、私が、実際に使っているものを加工したものです。

全部、同じの動きをさせるなら、
まず、フォームコントロールのドロップダウンを貼り付けて、どこか空いているセルに
------
(空白)
A
B
C
----
と入れ、右クリック－コントロールの書式設定－コントロール
入力範囲に、例えば、$K1:$K4 とします。
右クリック－マクロの登録で、新規登録 すると、

標準モジュールに、ドロップ1_Change() というマクロ・プロシージャができますから、
後は、以下のように、範囲が飛び飛びになるなら、RowsHidden(ShNames, "2:8, 10:12")のように、「,(カンマ)」区切りしてください。

なお、ActiveX コントロールのComboBox は、あまり、お勧めしません。

それぞれのシートの挙動が違うというなら、前回の解答のほうが近い気がします。

'//標準モジュール
Sub ドロップ1_Change()
  Dim ShNames
  ShNames = Array("Sheet1", "Sheet2", "Sheet3")
  Application.ScreenUpdating = False
  Call RowsHidden(ShNames, "")　'一旦全部戻す
  Select Case ActiveSheet.DropDowns(1).Value
　'ドロップダウンの一番上を開けているので、そこが1になる
　Case 2 'A
　  Call RowsHidden(ShNames, "2:8")　'複数範囲設定可 "2:8,10:15"(カンマ区切り)
　Case 3 'B
　  Call RowsHidden(ShNames, "9:15")
　Case 4 'C
　  Call RowsHidden(ShNames, "16:21")
　Case Else
　  Call RowsHidden(ShNames, "")
  End Select
  Application.ScreenUpdating = True
End Sub
'サブルーチン
Sub RowsHidden(ShNames As Variant, ByVal strRng As String)
Dim s As Variant
Dim Ar As Variant
Dim r As Variant
  For Each s In ShNames
　If strRng Like "#*" Then
　  Ar = Split(strRng, ",")
　  For Each r In Ar
　  Worksheets(s).Rows(r).Hidden = True
　  'もし、範囲に空白値が入るようなら、Rows(Trim(r)) としてください。
　  Next
　Else
　  Worksheets(s).Rows.Hidden = False
　End If
  Next s
End Sub

WindFaller · Answer

#5の回答者です。

ご試用ありがとうございました。

このメリットは、使っているフォームコントロールの場所を選ばないということです。私は、オートフィルタの下向き三角の左に置いているという、ふつうでは、考えられないような使い方をしています。

それと、Worksheet_Change のイベント起動を入力値によって制御しないと、すぐに起動してしまうわけですね。その点、フォームコントロールは、誤動作がすくなく、安定して使えるというメリットがあります。

シートをまたいで処理をするマクロというのは、やはり標準モジュールに書くほうが優れているのではないかと思うのです。厳密言うと制約が少ないのです。他にも、ThisWorkbook を使うという方法がありますが、未だに私は、使いこなせていません。とても、むつかしいです。

fujillin · Answer

No4です。

スマホからなので、具体的なコードは示せませんが、

＞Rows("■:■").Hidden = True
＞を他のシートに反映させる記述が知りたかったです。
もしかして、そのまま記述していませんか？
そのままだと、暗黙の指定でアクティブシートが対象になります。
No4にも書いておきましたが、
　Worksheets(SNames(i)).Rows("■:■").Hidden = True
のように、シートを明示して記述する必要があります。

各シートとも同じ処理なら、表示／非表示の範囲を決めるのはループの外で行う方が効率的です。

fujillin · Answer

No2です

＞Rows("10:19").Hidden = True
A1セルの値が変わったら、内容にお構いなしに無条件で消しっぱなしってことでしょうか？
誤操作でさわっちゃった場合とかいろいろありそうなので、よくわかりませんね。

とりあえず、複数のシートのA1セルを常に連動させる例を以下に示しておきます。
以下をThisWorbookモジュールに記載して、A1セルの値を変更してみてください。
※ 複数シートがご質問文の内容で固定なのか不明でしたので、列挙すれば良い形式にしてあります。（シート数が増減しても動作するはずです）

※ 各シート内で同じ処理を繰り返したい場合は、「ここに記述」のところに適切に記述しておけば、各シートで繰り返して処理されます。
（何をどうなさりたいのかよくわからないので、私には記述できませんでした）
※ 追記・修正等を行って、間違いなく動作することを確認できたなら、Application.EnableEvents = False/True の記述をForループの外側に出してしまった方が効率的な処理になります。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　Dim c, i, f, SNames

'//　対象とするシートを列挙で指定
　SNames = Array("Sheet1", "Sheet2", "Sheet3")

'// A1セルかどうかをチェック
　Set c = Target(1)
　If c.Address <> "$A$1" Then Exit Sub
'// 指定シートかどうかをチェック
　f = False
　For i = LBound(SNames) To UBound(SNames)
　　If Sh.Name = SNames(i) Then f = True
　Next i
　If Not f Then Exit Sub

'// 指定シート内で処理を繰り返し
　For i = LBound(SNames) To UBound(SNames)
　　Application.EnableEvents = False
　　Worksheets(SNames(i)).Cells(1, 1).Value = c.Value
　　Application.EnableEvents = True

'// 必要に応じて各シートでの処理内容をここに記述する
　　'// シートオブジェクトは Worksheets(SNames(i))
　Next i
End Sub

WindFaller · Answer

こんにちは。

Sheet1 で起きたことが、Sheet2 で同時に起きるかどうかは、プログラムで実行しているから、そうなるはずだということで、実際に、Sheet2にそうなっているか開けるまでは分からないという矛盾は潜んでいます。

>Worksheet.Change イベントはSheet1でしか働かないため、Sheet2、Sheet3の行の表示・非表示が連動しません。

一体、どのようなロジックを持って、どう望んでいるかだけで、具体的なものがないので良く分かりません。関数の正確なトリガー（イベントの起動)の場合は、同じシート内に限るので、シートをまたぐ場合は、別なイベントを取らないといけないような記憶があります。

ただ、サブルーチンを作って、Sheet2 を指定するとか、Sheet3 を指定する方法なら、それに越したことはありません。

簡単なサンプルを書きます。
'------------------------
Sheet1 のa からc を入力した時に
Sheet2　（一定の文字列がA列に並んでいる)
Sheet3　　（〃　　　　)
のA列から、a から c の文字を探して、それを行を非表示にし、
非表示になっていれば、再表示させる、というコードです。

イベント・ドリブン型
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long
  If Target.Column <> 1 Then Exit Sub 'A列のみ
  If Target.Count > 1 Then Exit Sub 'セルは1つのみ
  If Target.Value = "" Then Exit Sub 'Delで消す時は除外
  If Target.Value Like "[a-c]" Then '入力した値が、aからc の場合は、
　For i = 2 To 3
　'サブルーチンを起動
　  Call OtherSheetExec(Target.Value, Worksheets("Sheet" & i))
　Next i
  End If
End Sub
'サブルーチン
Sub OtherSheetExec(ByVal myVal As String, sh As Worksheet)
  Dim c As Range
  With sh
　For Each c In .Range("A3", .Cells(Rows.Count, 1).End(xlUp))
　  If c.Value = myVal Then
　　If c.EntireRow.Hidden = False Then
　　  c.EntireRow.Hidden = True　'非表示
　　Else
　　  c.EntireRow.Hidden = False　'表示
　　End If
　  End If
　Next
  End With
End Sub

fujillin · Answer

こんにちは

方法はいくつか考えられます。

現在の延長的に考えるなら、各シートでイベントが発生したら行う処理の内容を対象シート（Sheet1～3）全部に対して行うように修正しておけ宜しいでしょう。
一方で、Sheet2、Sheet3のセルを =Sheet1!A1 となさっているようなので、Sheet2、Sheet3でのChangeイベントはもうキャッチする必要はないのかもしれませんね。
その場合には、Sheet1だけにChangeイベントを設定し、処理内容を上記のようにしておけば良さそうに思われます。

別の方法として、どのシートを変えても同じように処理が行われるようにすることを考えてみます。
各シートにChangeイベントを設定しておいても良いですが、その後の処理は同じだと思われますので、WookbookのSheetChangeイベントにまとめて設定しておくほうが便利かと思います。
WorkSheetのイベントでは、イベントが発生した「シート」及び「Target Range」が引数として利用できますので、どのシートで発生しているかの識別も可能になります。
処理の最初に、当該シートのA1の値を他のシートにも反映するようにしておけば、関数を設定しなくとも、どのシート上で値を変更しても、各シートの値が連動するような仕組みにすることができます。
（関数利用だと、値を変更できるセルが限定されてしまいますよね？）
その上で、対象とする各シートで順に必要な処理を行うようにしておけば宜しいのではないでしょうか。

他にも方法や考え方はいろいろあると思いますが、一例として。

くんこば · Answer

Worksheet.Changeって書くと、デフォルトでカレントシートになります。
シートを指定して.Changeメソッドを呼び出せばいけるはず。

Worksheetsコレクション

Worksheet.Change イベントを他のシートにも反映させる方法？

#3の回答者です。

#5の回答者です。

No4です。

No2です

こんにちは。

こんにちは

Worksheet.Changeって書くと、デフォルトでカレントシートになります。

似たような質問が見つかりました

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

関連するカテゴリからQ&Aを探す

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

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

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