EXCELでの変数によるMacro自動実行について

質問

すいませんが教えてください・・・・・・

EXCELシートにツールボックスより、コマンドボタンを2つ配置しました。
（AUTOMATICはPublic変数で標準モジュールで定義しています
Dim AUTOMATIC As Boolean ）

------------------------------------------------------------------
CommandButton_1 :

Private Sub CommandButton1_Click()
AUTOMATIC = True
End Sub

CommandButton_2 :

Private Sub CommandButton2_Click()
AUTOMATIC = False
End Sub

------------------------------------------------------------------

あとはEXCELを起動中に、AUTOMATICが Trueでセル（例えばA2のセル）の
値が100のときには　Macroを起動したいのですが、どこにどう記述したら
いいか教えて欲しいのです。

------------------------------------------------------------------
CommandButton_1 :

Private Sub CommandButton1_Click()
if Cells(2,1) = 100 then 'A2はCells(2,1)
Macro実行
end if
End Sub

とAUTOMATICを使用せずに記述しても、毎回CommandButton_1をクリックしな
ければMacroが起動することができないので煮詰まってしまいました。
（一度CommandButton_1をクリックしておくとあとはA2セルを監視するだけ
でMacroを起動させたい）

すいませんがわかる方、教えていただけないでしょうか
よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

ご質問の文章だけでは分かりません。

1>　AUTOMATICが Trueでセル
2>（例えばA2のセル）の値が100のときには　Macroを起動したい

>（一度CommandButton_1をクリックしておくとあとはA2セルを監視するだけでMacroを起動させたい）

この部分の「監視」という意味ですね。Worksheet_Changeイベントで出来ればよいのですが、そうとは限らないことがあります。私は、この種の問題は、3度の内、2回は、迷宮入りにしてしまいました。監視セルは、自動的に変わらない、人間が換えていると思い込んでいると、そうではないものがあります。だから、Calculate イベントでよいと思い込んでいたときもあります。

問題は、「A2のセル」が何によって、値が変化するかだと思います。

補足

ありがとうございます。そうです。監視という意味なのです。
それにA2セル自体が他のブックから、ハイパーリンクにて貼り付け
されている（ここまでちゃんと記述するべきでしたね）ので
単純にWorksheet_Changeではうまくいかないのです。

なので上記の観点から言えば、人間がセルの値を書き換えたわけ
ではなく、自動的に書き換えられたということになります。

回答No.5

Wendy02

こんにちは。Wendy02です。

結局、私の回答のほうは、こちらの予想とは反していましたので、こちらの話は、一応、中断です。

Calculate イベントは、負担が大きいので、できる限りは避けたいひとつのイベントなのですが、やむをえないです。シートによっては避けたほうがよいでしょう。

>CalcurateコードでA2セル（リンクしているセル）の値
>が変化したときにMacroが実行することができました。
>あとはA2の内容を監視できればよいのですが。。。

「A2を監視」といっても、内容というのはなんですか？
「値」ではないのですか？その内容なら、Formula で、文字列比較をすればよいではありませんか？

ただ、外部リンクの変化を、Calculate イベントでとるというのは、聞いたことがありません。それは、実質的に、二つのシート(Window)を開けている時できるだけに成立する話です。そうでなければ、Sheet のActivate 時だけでよいです。それ以外では、DDEでしかありえませんが、その場合は、Calculateイベントでは取りません。

もう少し、具体的に詳細にお話してくださったほうが、より早い解決が望めます。

お礼

たびたびの御回答ありがとうございます。

> Calculate イベントは、負担が大きいので、できる限りは避けたいひとつのイベントなのですが、
> やむをえないです。シートによっては避けたほうがよいでしょう。

了解です。ただ今回はやむを得ないです。

>> CalcurateコードでA2セル（リンクしているセル）の値
>> が変化したときにMacroが実行することができました。
>> あとはA2の内容を監視できればよいのですが。。。

>「A2を監視」といっても、内容というのはなんですか？
>「値」ではないのですか？その内容なら、Formula で、文字列比較をすればよいではありませんか？

> ただ、外部リンクの変化を、Calculate イベントでとるというのは、聞いたことがありません。それは実質的に
> 二つのシート(Window)を開けている時できるだけに成立する 話です。そうでなければ、Sheet のActivate 時だ
> けでよいです。それ以外では、DDEでしかありえませんが、その場合は、Calculateイベントでは取りません。

値は値なのですが、その値はあるユニットの中のレジスタ値なのです。
メーカからはExcelアドインが提供されていまして、このアドインを使用したら決められた時間や周期でレジスタ
を読んでくれるExcelアドインです。ただ、このExcelアドインは、他にVBAマクロを組んでしまうと止まってしま
うのです。そこでもう1つのEXCELファイルで使用したいデータをリンクさせて、うまく表示させるためのVBAマク
ロをそのファイルに埋め込んでリンクしているデータの変化に応じて処理させてしまおうと考えました。

回答No.4

FEX2053

Worksheetのchangeイベントは、セルに対して実質的に値が変更されないと
トラップしてくれません。具体的に言えば直接セルにデータを入力したとき
などです。"="で参照する場合は、相手が他ブックだったりすると動きません。

ということで、caluculateイベントを用いて見て下さい。

>次の使用例は、ワークシートが再計算されると、必ず A 列から F 列までのサイズを調整します。
>
>Private Sub Worksheet_Calculate()
> Columns("A:F").AutoFit
>End Sub

当然、そのブックが「再計算」されないと何もおきません。このイベントが
キックされるかどうかは、やってみないと分かりません。なんにしたところで、
この手の処理はマクロ内に変数を設定して、そちらをキックに処理を起動する
のが常道で、「関数で参照されるセルが変更されたとき」をキックにするのは
非常に難しいのです。

あと、「姑息な手段」としては、当該セルのリンク元ブックのシートに
ついてchangeイベントを用いてマクロを起動し、そのマクロの中で、
当該のブックを開いてマクロを「workbook_open」で自動実行させて
しまう・・・という荒業もあることはありますが、毎回ブックをopen/close
するために、何かあった時に大変なのであんまりお勧めできません。

お礼

たびたびありがとうございます。A2セルの内容までは見切れてはいない
のですが次のCalcurateコードでA2セル（リンクしているセル）の値
が変化したときにMacroが実行することができました。
あとはA2の内容を監視できればよいのですが。。。

ワークシートのコード

Dim AUTOMATIC As Boolean
Dim adat As Variant '内容を覚えておくグローバル変数

Private Sub Worksheet_Calculate()
With Application.ActiveSheet.Range("A2")
If adat <> .Value And AUTOMATIC = True Then
Macro実行
End If
adat = .Value '入替
End With
End Sub

回答No.3

Wendy02

こんにちは。#2のWendy02です。

>なので上記の観点から言えば、人間がセルの値を書き換えたわけではなく、自動的に書き換えられたということになります。

やっぱりそうでしたのですね。#2875498 の質問の延長のようで、そうではないようですね。

>それにA2セル自体が他のブックから、ハイパーリンクにて貼り付けされている（ここまでちゃんと記述するべきでしたね）

でも、ハイパーリンクは、単に記述だけで、それ自体は何も変化しないと思いますが。クリックして、始めて、ハイパーリンク先が開きます。

そのほかでは、外部リンクがあります。しかし、外部リンクは、何もしないと、値はそのままです。それが、どの時点で変化するかによって、コードの方針が決まります。

それを教えていただかないと、次のステップには進めません。

お礼

たびたびありがとうございます。お粗末ながら、ハイパーリンクと外部
リンクの違いがいまいちわかってないのですが。。（汗!!）

外部リンクになると思います。ブック間で値をコピーするときに
右クリックでリンク貼り付けをしていますので、ただEXCELによっては
ハイパーリンクにラジオボタンがついている場合がありハイパーリンク
といってしまいました（EXCELのバージョンは2000）。

今、Calcurateで少しだけ記述しているところです。

回答No.1

FEX2053

そういう場合は、ワークシートのCHANGEイベントを監視します。
例えばこんな感じですね（Excelのヘルプから抜粋）

>ワークシートのセルがユーザーまたは外部リンクにより変更されたときに発生します。
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>
>Target 変更された範囲が渡されます。複数のセルを渡すことができます。
>
>次の使用例は、変更されたセルの色を青色にします。
>
>Private Sub Worksheet_Change(ByVal Target as Range)
> Target.Font.ColorIndex = 5
>End Sub

この場合、シート上の何かのセルが書き換わることがキックになりますので、
A2セルが100の状態であるのなら、どのセルが書き換わっても起動します。
A2セルが100に「始めてなったとき」にだけ起動したいなら、マクロに
もう一工夫必要なのですが、ここまでのコードがご自分で書ける方なら
難しい修正じゃないですよね？

お礼

ありがとうございます。しかし、上記の方にも捕捉しましたように
A2セル自体が他のブックからハイパーリンクされているので、
Worksheet_changeでは、うまくいかなかったのです。。。
とにかくありがとうございました。