excel vba/セルの値がOKになったら図形を消す

はじめまして。
VBAはそんなに詳しくないです。

エクセルで、例えば、A1:A5が全てOKになったら、表示している図形を消す。

という事をやりたいです。

セルの値によって表示させる、参照でINDIRECTを使用するものは使いたくないです。

A6に、「｛＝IF（and（A1:A5＝"OK"）,TRUE,FALSE）｝」という式を入れて全てOKになったら、TRUEとなるようにしています。

TRUEになった時に、表示している図形を非表示にする。
FALSEになったら、図形を表示する。

というVBAを作りたいですがうまく行きませんでした。

マウスでチェックボックスをクリックする事で図形のオンオフは出来ましたが、セルの値を変えてチェックボックスをオンオフさせても図形のオンオフができませんでした。
※本来チェックボックスは使用したくないです。
チェックボックスでオンオフ出来るのは知っていたので、計算式でオンオフさせても連動するかと思い試してみましたがダメでした。
使用したVBAは
Private Sub CheckBox1_Click()
.Shapes("myshape").Visible = range("A6").value
End Sub
です。

チェックボックス無しでオンオフするVBAを教えていただけないでしょうか。

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2019/08/04 01:51

No.1の回答者です。

途中から話が変わってくるとやりにくいですね。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$BE$14" Then 　←これでは、コールしないです。イベントが発生していません。
Select Case Range("BE14").Value

Target (該当するセル)そのものからイベントが発生するわけではないからです。
もちろん、どんなイベントでも、自作イベントなら、イベントは可能にしますが、既存のイベントを利用したいというのが、こちらの本音です。No.2さんのCalculate イベントは、Now関数など、揮発性関数を使い自分で値が変わる関数をおいてあげないと、イベントが発生しなかったと思います。

イベントはどこであろうが、Range("BE14")の値を取れば済むことですが、それでは頻繁にイベントが発生してしまうので、除外項目が必要です。

Worksheet_Change は、[Enter] によって起動しますから、

[BE14] ここに、数式があって、OK/NGの文字が入るというのでしょうか。

｛＝IF（and（A1:A5＝"OK"）,"OK","NG"）｝
むろん、TRUE/FALSE なら、=COUNTIF($A$1:$A$5,"OK")=5 これで済むわけですが。
今回は、=IF(COUNTIF($A$1:$A$5,"OK")=5,"OK","NG")
に入れ替えました。

>例えば、A1:A5が全てOKになったら、

どういうイベントで、OK/NGの値が変わるか、質問では肝心な所は書かれていませんので、これでうまくいくとは限りません。
例えば、1.ワークシートへの手動による入力、2.OLEコントロールでの自動変更、3.DDEからの自動変更、これらの条件によってイベントは変わる可能性が強いです。だから、以下が有効なのは、1に限ります。

'//シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
　 If Intersect(Target, Range("BE14").Precedents) Is Nothing Then Exit Sub
　 If Range("BE14").Value = "OK" Then
　 Shapes("MyShape").Visible = msoTrue
　 Else
　 Shapes("MyShape").Visible = msoFalse
　 End If
End Sub

ただ、このイベントは、シートをまたいで別のシートからイベントが取れないと思います。その場合は、途中の中継点が必要だったような気がします。

この回答へのお礼

最初のTRUE/FALSEでうまくいかなかったので途中で仕様を変えてしまいました。
大変申し訳ありません。

WindFallerさんのおっしゃる通り、Worksheet_ChangeではEnterを押したときしか反応しませんでしたので、fujillinさん（No.2さん）のヒントと、WindFallerさんから頂いた回答を元に、再度組みなおしてみたところ成功することが出来ました。

A1：A5についてですが、それぞれ計算式が入っています。

＊＊＊A3~A5に入っている計算式
=IF(B1="","NG","OK")
※A2には「B2＝””」が入っています

B1~B2に入っている計算式は
=IF(P7="","",1)
B1はP7、B2はR7とリンクしていて、P7,R7に何か入力されたらA1:A2がOKとなるようにしています。

＊＊＊A3~A5に入っている計算式
=IF(B3=0,"NG","OK")
※A4には「B4＝0」、A5「B5＝0」が入っています。

B3~B5はオプションボタンとリンクしていて、それぞれどれかにチェックが入ったら１～５の数字が出るようになっています。
リンクさせているオプションボタンそれぞれにチェックが入ればOKとなるようにしています。

そして、これらのA1～A5がすべてOKになればBE14がOKの文字が出るようにしました。

詳しい説明を省いてしまい大変お手数おかけしました。
本来でしたら、お二人から頂いたご回答どちらも参考にさせていただいて成功することが出来たので、どちらの回答にもベストアンサーを付けたいのですが、いろんな可能性を考えて頂いたWindFallerさんをベストアンサーとさせていただきました。

最終的に以下でうまくいきました。
Private Sub Worksheet_Calculate()
Select Case Range("BE14").Value
Case "NG"
ActiveSheet.Shapes("myshape").Visible = True
Case "OK"
ActiveSheet.Shapes("myshape").Visible = False
End Select
End Sub

本当にありがとうございました。

お礼日時：2019/08/04 10:05

No.3 回答者： fujillin 回答日時： 2019/08/02 14:23

No2です

＞指定したコレクションに対するインデックスが境界を超えています
図形をうまく指定できていない雰囲気ですね。

Shapesオブジェクトはブック全体で共通のようですが、別シート上の図形になっているとかはないですよね？
あるいは、図形を名前ではなくインデックス（＝番号）で指定しても取得できませんか？
または、Shapesを .Shapesとしてみるとか・・・


エディタのウォッチ式でShapes等を調べてみると、なぜ取得できないのかに関するヒントが得られるかも知れません。

この回答へのお礼

返答が遅れてしまい申し訳ありませんでした。

やはり、shapesの値が取得できていませんでした。

fujillinさんに頂いたアイデアを参考にCalculateを用いて再度考え直して成功する事が出来ました。
ありがとうございました！

Private Sub Worksheet_Calculate()

Select Case Range("BE14").Value
Case "NG"
ActiveSheet.Shapes("myshape").Visible = True
Case "OK"
ActiveSheet.Shapes("myshape").Visible = False
End Select

End Sub

お礼日時：2019/08/04 09:19

No.2 回答者： fujillin 回答日時： 2019/08/02 12:54

こんにちは

A6セルには、ご提示の関数式が設定されているものとして…
アイデアのみですが、シートモジュールに以下を設定しておくのではいかが？

Dim B As Boolean
Const spName = "myshape"

Private Sub Worksheet_Activate()
B = Shapes(spName).Visible
End Sub

Private Sub Worksheet_Calculate()
If Range("A6").Value <> B Then
B = Range("A6").Value
Shapes(spName).Visible = B
End If
End Sub

この回答へのお礼

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

モジュールに記述したところ、

下から3行目の
〉shapes(spNane).Visible =B
で、指定したコレクションに対するインデックスが境界を超えています。とエラーが起きてしまいました。

自分なりにアレンジしてみたのですが結局はダメでした…。

もう少し勉強します。。

お礼日時：2019/08/02 13:58

No.1 回答者： WindFaller 回答日時： 2019/08/01 22:26

>計算式でオンオフさせても連動するかと思い試してみましたがダメでした。

'//シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A6") Then
　 Shapes("MyShape").Visible = msoTrue
Else
　 Shapes("MyShape").Visible = msoFalse
End If
End Sub

しかし、数式自体が他から変更を受ける場合は、別のイベントがが必要になります。

この回答へのお礼

お返事ありがとうございます。

その後試行錯誤した結果、下記にたどり着いたのですが、WindFallerさんが仰る通り、数式は他から変更を受けるため反応しませんでした。

何か方法を教えていただけますでしょうか。

下記は、BE14に指定した範囲のセルが全てOKになったらOK、そうじゃなければNGかを表示する計算式が入っています。
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$BE$14" Then
Select Case Range("BE14").Value
Case "NG"
ActiveSheet.Shapes("myshape").Visible = True
Case "OK"
ActiveSheet.Shapes("myshape").Visible = False
End Select
End If

End Sub

お礼日時：2019/08/02 08:06