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

エクセル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

No.3 ベストアンサー 回答者： GooUserラック 回答日時： 2016/06/22 20:35

関数の戻り値が変わった場合は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
--------------------------------------------------------------

この回答へのお礼

遅くなりましたが、ありがとうございます。
書いていただいたコードでうまく動きました。

お礼日時：2016/06/28 17:09

No.5 回答者： ママチャリ 回答日時： 2016/06/23 20:31

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

まず、下記の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

この回答へのお礼

ありがとうございます。
関数の戻り値が変更された場合でも、イベントを発生させる方法があるということですね。
しかし、誠に申し訳ないのですが、この度は理解できないまま締切の期限を迎えてしまいました。

お礼日時：2016/07/07 18:02

No.4 回答者： WindFaller 回答日時： 2016/06/22 20:40

このご質問の内容は、古典的で、昔は、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

この回答へのお礼

ご回答いたがき、ありがとうございます。
内容が理解できなかったため、どうお礼を書こうかと思っているうちに遅くなってしまいました。

お礼日時：2016/07/07 17:43

No.2 回答者： GooUserラック 回答日時： 2016/06/22 18:52

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

仕方が無いので、ChangeイベントにCall命令を書き込んで標準モジュールで処理することになります。

この回答へのお礼

ありがとうございます。
しかし、回答していただいたことについて理解できなかったのですが、具体的にどのように修正すれば良いのか教えていただくことは可能でしょうか。

もしくは、関数を入力してあるセルの戻り値が変わった場合でも、Changeイベントを発生させることはできないでしょうか。

お礼日時：2016/06/22 19:04

No.1 回答者： GooUserラック 回答日時： 2016/06/22 17:36

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

もちろん処理するコードはシート名などチャント指定しないととんでもない事になります。

この回答へのお礼

すみません。
他のシートをマクロで変更するのではなく、他のシートの指定したセルに変更があった場合に、ワークシートのChangeイベントを発生させたいと思っております。

お礼日時：2016/06/22 18:44