他のシートの特定のセルが変わると、自動実行されるマクロについて

Question

エクセル2010を使っている者ですが、ファイル中に「入力」という名前のシートが左から3番目にあるのですが、そのシートのＣ5の値を変更したときに、Ａ列が空白になっている行を非表示にするというマクロについてですが、以下のように書いても動きませんでした。

マクロを書いたシートのC5が変更になった場合はうまく動くので、
Worksheets("入力").Range("C5")
という書き方が問題だと思うのですが、どのように書けば良いのか、ご教示願います

もしくは、同じシートのC5は関数で値を取得しており、関数の戻り値で変更されてもマクロが起動しないようなのですが、戻り値での変更でも起動させるようにする方法をご教示願います。



Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Worksheets("入力").Range("C5")) Is Nothing Then
        Exit Sub
    Else
    
 Dim LastRow As Long
 Dim i As Long

 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
 For i = 1 To LastRow
 If Cells(i, 1) = "" Then Rows(i).Hidden = True
 Next i
 
 End If
  
End Sub

GooUserラック · Accepted Answer

関数の戻り値が変わった場合はChangeイベントは発生しませんね。
Calculate（再計算）イベントは発生しますが、どこが再計算されたかは判断できないので使えません。
修正方法ですが、処理やシート構成などが良く判らないので…処理コードのところは適当に修正してください。

☆入力シートのシートモジュールに
--------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$5" Then Call 処理
End Sub
--------------------------------------------------------------
☆標準モジュールに
--------------------------------------------------------------
Sub 処理()
Dim i As Long
  Sheets("Sheet1").Select
  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = "" Then Rows(i).Hidden = True
  Next i
End Sub
--------------------------------------------------------------

ママチャリ · Answer

関数が入っているセルの値が変わった時にマクロを動かす仕組みになります。
まず、下記のVBAを各オブジェクトの下に張り付けて下さい（非表示にするマクロは、qazxcvfr4さんの方で書き直してくださいね）。
その上で、「入力」シートのC5セルに入っている関数を下記のユーザ定義関数（MyChangeIf）で包みます。
具体的には、例えばC5セルに =IF(Sheet2!A1<10,"A","B") という式が入っている場合、それを =MyChangeIf(IF(Sheet2!A1<10,"A","B")) とします。

【ThisWorkbook】
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If MyChange Then
        Call 非表示にするマクロ
        MyChange = False
    End If
End Sub

【標準モジュール】
Public MyChange As Boolean  'パブリック変数で定義。

Function MyChangeIf(v As Variant) As Variant
    If Application.ThisCell.Text <> CStr(v) Then
        MyChange = True
    End If
    MyChangeIf = v
End Function
    
Sub 非表示にするマクロ()
    MsgBox "Ａ列が空白になっている行を非表示にするというマクロ"
End Sub

WindFaller · Answer

このご質問の内容は、古典的で、昔は、Microsoft 側のサポートに載っていましたが、いつのまにか消えてしまいました。特に、DDEから自動的に入ってくるものを取れないかという内容でしたが、今は、ActiveX やCOMアドインで、知らないままにセルの値が変わっていたりしますので、そちらがわで処理することが多いようです。

私の記憶違いかもしれませんが、Change イベントは、Enter (CRコード）が必要だったような気がします。CRコードがないものは、イベントが走りません。Calculate イベントも、本来、計算をしないと走らないので、やや扱いに難があります。必ず、そのシートに計算式があることが条件です。

そして、最大級は、API時計を使ってイベントを自分で作ってしまうことです。しかし、そんなものは常に時限爆弾を抱えているようなマクロになってしまいます。

以下は、難易度最大を10としたら、以下のマクロはレベル2ぐらいです。

でも、これって、そのままなら、一回きりのマクロですよね。（^^;

注意点は、一旦閉じて、ファイルを開ければ、myVal の中に値が入りますが、そうでなければ、人為的にmyValに値を入れたほうが良いと思います。
Private ステートメントがついていますから、ThisWorkbook モジュールで、

Sub Test1()
　myVal =Me.Worksheets("入力").Range("C5").Value
End Sub
と入れてください。

不安が残るなら、別のイベントで、値を入れてください。
マクロを書いた最初だけです。後は、ファイルを開けるとき、マクロが実行されますから、大丈夫です。

同じ型のマクロは、標準モジュールでも作れます。こちらが古いスタイルです。

'ThisWorkbook モジュールに貼り付けてください。

Private myVal As Variant　'モジュールスコープ変数
Private Sub Workbook_Open()
　With Me.Worksheets("入力")
　　　　myVal = .Range("C5").Value　'値を入れる
　　End With
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim c As Variant
　　If Me.Worksheets("入力").Range("C5").Value <> myVal Then
　　　　For Each c In Me.Worksheets("Sheet1").Range("A1:A10")
　　　　　　If c.Value = "" Then
　　　　　　　　c.EntireRow.Hidden = True
　　　　　　End If
　　　　Next c
　　　　myVal = Me.Worksheets("入力").Range("C5").Value
　　End If
End Sub

GooUserラック · Answer

変更が有ったシートでしか「Changeイベント」は発生しませんよ。
仕方が無いので、ChangeイベントにCall命令を書き込んで標準モジュールで処理することになります。

GooUserラック · Answer

シートモジュールでは、他のシートを変更する事は出来ませんので、標準モジュールに処理するコードを書き込んで、それを「Call ○○」と呼び出します。
もちろん処理するコードはシート名などチャント指定しないととんでもない事になります。

他のシートの特定のセルが変わると、自動実行されるマクロについて

関数の戻り値が変わった場合はChangeイベントは発生しませんね。

関数が入っているセルの値が変わった時にマクロを動かす仕組みになります。

このご質問の内容は、古典的で、昔は、Microsoft 側のサポートに載っていましたが、いつのまにか消えてしまいました。

変更が有ったシートでしか「Changeイベント」は発生しませんよ。

シートモジュールでは、他のシートを変更する事は出来ませんので、標準モジュールに処理するコードを書き込んで、それを「Call ○○」と呼び出します。

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

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

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

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

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