別シートのトグルボタンを指定したい

質問

複数シートがあって、それぞれのシートにトグルボタンがあるとします。
トグルボタンを押したときの動作は同じなので、
標準モジュールとかに動作内容を記述したいのですが、
「If ActiveSheet.ToggleButton1 = False Then」とか記述しても
エラーが出ます。
アクティブなシートにあるトグルボタン1が押された時の動作内容を書きたいです。

回答No.4

cj_mover · ベストアンサー

＃すみません、長くなるので分けます。（１／２連投）

通常の手順でサンプルを作成して多条件大量にテストしましたが、
ご提示の記述はエラーなく正常に機能し正しい判別を返すことを確認しました。（xl2010で確認）
以下を実行して確認しました。
' ' ====================シートモジュール==================
Private Sub ToggleButton1_Change()
　　Call マクロ0
End Sub
' ' ====================標準モジュール====================
Sub マクロ0()
　　If ActiveSheet.ToggleButton1 = False Then
　　　　MsgBox "Raised"
　　Else
　　　　MsgBox "Sunken"
　　End If
End Sub
' ' ======================================================

■　オブジェクト名の確認　■
エラーになる可能性として、
コントロールに表示されている文字列＝キャプション
と、
コントロールを指す一意な名前＝オブジェクト名
との食い違いがあることに気が付かないことがよくあるので、
念の為、オブジェクト名を確認してみてください。

例えば、
トグルボタンに表示されている文字列が
　　ToggleButton1
であっても、
ＶＢＡから参照するのに必要なオブジェクト名が
　　ToggleButton2
などに書き換わっている場合があります。
（例えばToggleButton1のコピーを作成した場合など）
この場合、ToggleButton1 が存在しない、と判断されると、
　　If ActiveSheet.ToggleButton1 = False Then
の行でエラーになり、添付画像のメッセーシが表示されます。
以下、エラーの種類が同じならば、という限定的な対処法です、

まず、（手作業で確認するのも大変でしょうから）
すべてのシートに存在するすべてのトグルボタンについて
そのオブジェクト名とキャプションを確認するマクロを書きました。
MsgBoxを表示するだけなので、害はありませんから、
ひとまず、これを実行して確認してみてください。
' ' ====================標準モジュール====================
Sub CheckTgl8276008()
　　Const RegName = "ToggleButton1"
　　Dim sh As Worksheet
　　Dim oOle As Excel.OLEObject
　　Dim sMsg As String
　　sMsg = "各シートのトグルボタン" & vbLf & "シート名" & vbTab & "オブジェクト名" & vbTab & "キャプション"
　　For Each sh In Worksheets
　　　　For Each oOle In sh.OLEObjects
　　　　　　If TypeName(oOle.Object) = "ToggleButton" Then
　　　　　　　　sMsg = sMsg & vbLf & sh.Name _
　　　　　　　　　　& vbTab & oOle.Name & IIf(oOle.Name = RegName, "", "◆") _
　　　　　　　　　　& vbTab & oOle.Object.Caption
　　　　　　End If
　　　　Next
　　Next
　　MsgBox sMsg
End Sub
' ' ======================================================
●マクロの実行結果として
１●表示されたオブジェクト名が、
前提としている名前 ToggleButton1 と異なる（ものが含まれる）場合は、
名前を合わせてやれば解決です。
マクロ側でオブジェクト名を変えるだけで済む場合もあるでしょう。
一部のトグルボタンのオブジェクト名を変える方が簡単な場合もあるでしょう。
オブジェクト名の変更は、
　　デザインモード　→　トグルボタンを右クリック　→　プロパティ　→　オブジェクト名
の順に辿って、書き換えるだけです。
ひとつのシートに重複した名前を付けられない点にだけ注意してください。
２●表示される 筈 のオブジェクト名が、
表示されない場合は、恐らく、
フレームコントロール上に配置されたトグルボタン、だったということだと思います。
この場合は、オブジェクトの指定の仕方を書き換える必要があります。、
　　If ActiveSheet.Frame1.Controls("ToggleButton1").Value = False Then
ここで記す Frame1 ToggleButton1 どちらもオブジェクト名です。
フレーム上に配置したトグルボタンのオブジェクト名、確認や変更は、
　　デザインモード　→　フレームを右クリック　→　フレームオブジェクト / 編集　→　トグルボタンを右クリック
　　→　プロパティ　→　プルダウンメニューから Name をクリック　→　上部入力ボックスにて Name を修正　→　適用をクリック
という手順になります。
ひとつのフレーム内に重複した名前を付けられない点にだけ注意してください。
尚、フレーム上に配置したコントロールでのイベントに関しては
http://okwave.jp/qa/q8271250.html
こちらを参考にしてください。

■　トグルボタン（またはそのTempファイル）破損の可能性　■
今回ご質問の内容からすると、この手のトラブルなら、
もう少し違う説明になるでしょうから、可能性低いと思うので、簡単に済ませますが。
様々織り交ぜて、1000件程度のテストをしていた中で、
トグルボタンが（一時的に）破損（破断）するエラーを２度、確認できました。
原因の特定までは出来ていないので、偶然であって、再現は出来ないのですが。
この場合でも、マクロ0 実行時には添付画像のエラーが発生します。

■　余談　■
（記憶に残っていないので）明確な理由を挙げることが出来ませんが、
私個人としては、自分の手を離れて他人が編集するファイルでは
ActiveX コントロールの ToggleButton、ScrolBar、SpinButton などのコントロールは
シート上では使わないようにしています。
私なりの対策として、
（少なくとも、動作（ボタン操作時の反応）が軽快になるというはっきりとした理由もあって）
' ' ====================シートモジュール==================
Private Sub Label1_Click()
　　If Label1.SpecialEffect = fmSpecialEffectRaised Then
　　　　Label1.SpecialEffect = fmSpecialEffectSunken
　　Else
　　　　Label1.SpecialEffect = fmSpecialEffectRaised
　　End If
　　MsgBox Label1.SpecialEffect = fmSpecialEffectSunken
End Sub
' ' ======================================================
のように、同じ ActiveX コントロール ですが Label コントロールを使うようにしています。
名前を挙げたコントロールの中で私の経験上、一度も破損したことがないのは
Label コントロールだけなので、もしかして、こういうトラブルだった場合にでも、
思い出して、参考にしてもらえたら、と思います。
（もっとも、LinkedCellとか使っている場合など、自力実装になりますが）

（次投稿につづく）

お礼

ご指摘のようにオブジェクト名が間違っていました。

回答No.6

mitarashi

#2です。
「お礼」？への回答は、素晴らしい回答者の皆様にお任せして、望まれてはいないと思いますが、#2を汎用化したコードを投稿しておきます。ご参考まで。

'☆ThisWorkbook モジュール
Dim myToggleCtrls() As Class1

Private Sub Workbook_Open()
Dim i As Long
Dim sh As Worksheet
Dim shp As Shape

i = 1
For Each sh In Worksheets
For Each shp In sh.Shapes
If shp.Type = msoOLEControlObject Then
If shp.DrawingObject.progID = "Forms.ToggleButton.1" Then
ReDim Preserve myToggleCtrls(1 To i)
Set myToggleCtrls(i) = New Class1
Set myToggleCtrls(i).control = sh.DrawingObjects(shp.Name).Object
i = i + 1
End If
End If
Next shp
Next sh
End Sub

'☆標準モジュール
Sub test(myToggle As msforms.ToggleButton)
With myToggle
MsgBox .Parent.Name & vbCrLf & .Name & vbCrLf & CStr(.Value)
End With
End Sub

'☆Class1モジュール
Private WithEvents toggleCtrl As msforms.ToggleButton

Public Property Set control(newControl As msforms.ToggleButton)
Set toggleCtrl = newControl
End Property

Private Sub toggleCtrl_Click()
Call test(toggleCtrl)
End Sub

回答No.5

cj_mover

（２／２連投）

■　総合的な対策案　■
たぶん現在のマクロ呼び出しは、先にに挙げた
　　Call マクロ0
の例のような構造で試しているのだと思います。
> 「If ActiveSheet.ToggleButton1 = False Then」
この記述が原因でアクセスに失敗することは考えにくいですが、
なるべくエラーフリーの方向で幾つか挙げてみます。
' ' ====================シートモジュール====================
Private Sub ToggleButton1_Change()　　'　　◆例１～◆例５、択一
　　Call マクロ1　　' ◆例１
　　Application.OnTime Now, "マクロ1"　　' ◆例２
　　Call マクロ1_2　　' ◆例３
　　Call マクロ2(ToggleButton1)　　' ◆例４
　　Call マクロ3(ToggleButton1.Value)　　' ◆例５
End Sub
' ' ====================標準モジュール====================
Sub マクロ1()
　　If ActiveSheet.ToggleButton1.Value = False Then
　　　　MsgBox "マクロ1"
　　End If
End Sub
Sub マクロ1_2()
　　If ActiveSheet.OLEObjects("ToggleButton1").Object.Value = False Then
　　　　MsgBox "マクロ1_2"
　　End If
End Sub
Sub マクロ2(ByVal tglTarget As MSForms.ToggleButton)
　　If tglTarget.Value = False Then
　　　　MsgBox "マクロ2"
　　End If
End Sub
Sub マクロ3(ByVal flgTgl As Boolean)
　　If flgTgl = False Then
　　　　MsgBox "マクロ3"
　　End If
End Sub
' ' ======================================================
◆例１
　ほぼ、ご提示の原型通りです。
◆例２
　Excel一般機能（フィルタとか）でセルの表示非表示を切り替えたり
　セル範囲のスクロール（.Select も含む）などする場合にお奨め。
　イベントプロシージャを抜けてからマクロを実行します。
　エラーを解消するのに役立つケースがあります。
◆例３
　例１と比べるとアクセスが約３倍遅くなりますが、
　オブジェクト名を文字列で指定できるのが長所。
　「こう書くべき」と仰る方も少なからず居らっしゃるかと。
◆例４
　ToggleButton オブジェクトを引数として 値渡しにしてマクロを呼びます。
◆例５
　ToggleButton の .Value プロパティを取得し、
　論理値（Boolean）を引数として 値渡しにしてマクロを呼びます。

今回の課題については、上記◆例５（場合によっては◆例４）のような構造にするのがお奨めです。
具体的な処理全体を見ないと断言はできませんが、
見えている限りでは、最も一般的で適した構造だと思います。
契機になったボタンを探し直さなくてもいい、という意味です。

■　蛇足　■
多数のコントロールのイベントを一様に一カ所に記述する、ということでしたら、
新規のクラスモジュールを設けるのがお奨めです。
ただ、現状エラーの原因が特定されていませんから、付け加えると、
エラー発生時に、デバッグして、実行中のプロシージャをキチンと抜けるなら問題ないのですが、
[終了]、[リセット]、[デザインモード]などで終らせる場合は、
クラスの設定をやり直さないといけません。
これを知らないと、おや？ってことになるかも知れないと思いましたので念の為。

以上です。

回答No.3

MarcoRossiItaly

検証はしてないですが、きっと No.2 さんのご回答でうまく行くのでしょう。もちろん、簡単に No.1 さんの方法でもいいですね。ベストアンサーは辞退します。

＞セルの場合は、ActiveSheet.Cells(1,1)と書けるのに
　トグルボタンはActiveSheet.Togglebutton1と書けないのなぜなのでしょうか？

「ActiveSheet.ToggleButton1」あるいは「ActiveSheet.ToggleButton1.Value」という記述そのものは、ひとまず文法的に誤っていません。さらに、トグルボタンのあるシートモジュールに書いているときは、「Me.ToggleButton1.Value」というのもオッケーだし、「ToggleButton1」しか書いてなくても True / False になるので平気だったりします。

エラーが出ているのは多分そこじゃなく、どこなのかは分かりませんが、とりあえずイベントドリブン（イベント駆動型）のプログラムにしたいのに、オブジェクトがないぞということだと思います。シートやクラス（のインスタンス）はオブジェクトですが、標準モジュールということは、オブジェクトがない。

例えばトグルボタンを置いたシートのモジュールならば、「Private Sub ToggleButton1_Click()」とか「Private Sub Worksheet_Change(ByVal Target As Range)」という具合に、「オブジェクト_イベント」という名前のプロシージャを書きます。しかし標準モジュールでは、そうはならないということ。質問者さんとしては、クリックというイベントが起こった際に、何らかの処理をさせたいわけですよね？

なおモジュールというのは、宣言とプロシージャの集まったもののことです。だいたい同じ意味の英単語として unit がありますが、プログラミングでは module と言います。モジュールの集まりは、プロジェクトと呼んでいます。

回答No.2

mitarashi

シートモジュールにコードは書きたくないんだ！という場合です。
http://okwave.jp/qa/q8272300.htmlの応用編ですが、こちらはクラスモジュールを用いないと無理だと思います。
各シートにToggleButton（コントロールツールボックス）が一個ずつあり、名前はデフォルトのままで変更していないとします。
標準モジュールのプロシージャに、クリックされたトグルボタンの存在するシートと、ボタンの状態を渡しています。（後はお好きに料理して下さい）
動作説明だけのスケルトンですので、エラー処理とか、オブジェクトの解放とかはご自分で肉盛りをお願いします。
'☆ThisWorkbookモジュール
Dim myToggleCtrl() As Class1

Private Sub Workbook_Open()
Dim i As Long

On Error Resume Next
For i = 1 To Worksheets.Count
ReDim Preserve myToggleCtrl(1 To i) As Class1
Set myToggleCtrl(i) = New Class1
Set myToggleCtrl(i).control = Sheets(i).ToggleButton1
Next i
End Sub

'☆Class1モジュール
Private WithEvents toggleCtrl As MSForms.ToggleButton

Public Property Set control(newControl As MSForms.ToggleButton)
Set toggleCtrl = newControl
End Property

Private Sub toggleCtrl_Click()
Call test(toggleCtrl.Parent, toggleCtrl.Value)
End Sub

'☆標準モジュール
Sub test(mySheet As Worksheet, toggleValue As Boolean)
MsgBox mySheet.Name & vbCrLf & CStr(toggleValue)
End Sub

お礼

セルの場合は、ActiveSheet.Cells(1,1)と書けるのに
トグルボタンはActiveSheet.Togglebutton1と書けないのなぜなのでしょうか？

回答No.1

kmetu

それぞれのシートに

Private Sub ToggleButton1_Click()
標準モジュールの実行したいプロシージャ
End Sub

として

標準モジュールに

Function 標準モジュールの実行したいプロシージャ()

If ActiveSheet.ToggleButton1 = False Then
実行したいコード
End If

End Function

でいけませんか。