埋め込みコントロールはワークシートにコントロールを作成したものです
このコントロールはワークシートの広さから、往々にしてコントロールの数が多くなります
しかも、ほとんど同じ動きをさせることも多い場合があります
そんな状態で、1つ1つのコントロールにイベント処理を何個も作成していくのは、大変だし、メンテナンス性も劣化します
そんな時に使用するのが、コントロールをクラスでまとめて制御する方法です
これにはイベント最強説があるWitheventsを使用します
以下にその説の実の部分が書いてあります
クラスモジュールを作成する
まずはクラスモジュールの作成を行います
モジュールの作成自体は、標準モジュールと全く同じで挿入メニューから行えます
挿入方法に関しては以下の記事のユーザーフォームモジュールの挿入方法を参考にしてください
クラスモジュールが作成出来たら、そのモジュール内にコードを作成します
画像の様なコードを作成しました
プロジェクトウィンドウの青くなっている「Class1」モジュール内です
Dim WithEvents 作成 As MSForms.OptionButton
Sub 紐付け(作成コントロール As MSForms.OptionButton)
Set 作成 = 作成コントロール
End Sub
Private Sub 作成_Change()
If 作成.Value = True Then
Selection.Interior.Color = 作成.BackColor
Else: End If
End Sub
こちらは、クラスモジュール内に作成するコードなので、OptionButtonの取得であればコピペしてそのまま使用できます
Dim WithEvents 作成 As MSForms.OptionButton
この1行が集約するコントロールを代入させる変数の宣言です
WithEventsを使用することにより、イベントの作成が可能になります
MSForms.OptionButtonはオプションボタンのコントロールです
変数の型指定です、Object型は指定できません
Sub 紐付け(作成コントロール As MSForms.OptionButton)
Set 作成 = 作成コントロール
End Sub
ここで紐付け処理を作成しています
後述で出てくる紐付け作業の時に、実行されるプロシージャです
中身は引数のコントロールを上記の変数に代入させているだけです
ただ、これだけで以降は後述のイベント処理が実行されます
Private Sub 作成_Change()
If 作成.Value = True Then
Selection.Interior.Color = 作成.BackColor
Else: End If
End Sub
イベント処理です
内容は、OptionButtonがON（True）ならコントロールの背景色を選択範囲に適用するだけです
ここに作成したイベントが紐付けされたコントロール全てで実行されます
もし、イベント処理に変更があった場合はここの処理を変更すればいいだけです
コントロールの数が10個あろうが100個あろうが、ここで全てが集約されています
考えるだけで便利な話です
ここまでで、クラスモジュールの作成は完了です
シートモジュールの処理作成
クラスが作成出来たら、それを使用してシートモジュールに処理を作成します
画像の様なコードを作成しています
モジュールは青色になっている、「Sheet1」モジュールになります
Dim 動的作成() As New Class1
Private Sub Worksheet_Activate()
Dim 取得用 As Shape, インデックス As Long
For Each 取得用 In ActiveSheet.Shapes
If 取得用.Name Like "*OptionButton*" Then
ReDim Preserve 動的作成(インデックス)
動的作成(インデックス).紐付け OLEObjects(取得用.Name).Object
インデックス = インデックス + 1
Else: End If
Next 取得用
End Sub
Private Sub Worksheet_Deactivate()
Erase 動的作成
End Sub
Private Sub CommandButton1_Click()
Selection.Interior.ColorIndex = 0
Erase 動的作成
End Sub
こちらは、コントロールの名前が違っていると使用できませんのでコピペする場合は修正をしてください
Dim 動的作成() As New Class1
クラスを使用する場合には、クラスの作成が必要になります
この1行が、クラスの作成とその変数の宣言になります
コントロールの個数分この変数が必要になるため、変数は動的配列で作成します
またNewキーワードによりクラスがここで作成されています
Private Sub Worksheet_Activate()
Dim 取得用 As Shape, インデックス As Long
シートのActivateイベントを使用します、シートがアクティブ状態になったときに発生するイベントです
まずは使用する変数の宣言です
「取得用」はシートに配置されたコントロールをループ処理で代入させるための変数です。配置されたコントロールは全てShapesオブジェクトとなるため、型指定はShape型を使用します。ここには図形なんかも入ってきます
「インデックス」は動的配列のインデックス番号用の数値で使用します。整数なのでLong型を使用します
For Each 取得用 In ActiveSheet.Shapes
～～ 中略 ～～
Next 取得用
次にシートに配置されたコントロールの取得処理に入ります
ここではシート上のShapesオブジェクトをForEachループで1つずつ検証しながら取得を行っていきます
変数の取得用に各オブジェクトが代入されます
If 取得用.Name Like "*OptionButton*" Then
～～ 中略 ～～
Else: End If
オブジェクトの取得ができたら、そのオブジェクト名で取得可否の条件分岐を行います
If分岐で文字列比較を行います
今回はオプションボタンを取得するので、名前に「OptionButton」が含まれていたら処理を中略の処理が実行されます
含まれていなければ何も行いません
ReDim Preserve 動的作成(インデックス)
取得可能なコントロールが見つかったら、まずは動的配列を追加します
もちろんそれまでに取得したコントロールを削除されていは困りますのでPreserveキーワードを使用してください
変数インデックスはあとで加算しています
動的作成(インデックス).紐付け OLEObjects(取得用.Name).Object
インデックス = インデックス + 1
ついにクラス定義の出番です
ここまで長かったです、書いてる自分もお尻が痛いです
まず、クラス宣言した変数名ではクラスモジュールで作成したメソッドやプロパティが使用できます
今回は、紐付けという処理を作成しました
それはメソッドという形でここで使用することが出来ます
変数に対するメソッドなので、「.」を付けることでインテリセンス入力できます
これはCallによる処理の呼び出しでは無いので、引数に「()」は必要ありません
半角スペースを入れてから、引数を指定します
今回は「OLEObjects(取得用.Name).Object」となっています
まずもって、そのままでは取得できません
変数「取得用」がShape型だからです
なので、コントロールとしてのオブジェクトで指定する必要があります
OLEObjectsはシートにあるコントロールのコレクションです
その中から取得用.Nameで指定することで取得しているコントロールを指定することができます
指定したコントロールのObjectプロパティを指定することで最終的にコントロールが指定される状態になります
この時点でクラスの紐付けプロシージャが実行されて、クラスで宣言した変数「作成」に代入されます
ここ以降にクラスで作成したイベントがこのコントロールで実行されるようになります
紐付けが出来たら、変数「インデックス」に1を加算します
ここまでで、処理の作成が完了です、おっつかれした～
処理の動きと紐付けの解除
まずは、シートのアクティブイベントを実行する必要があります
Sheet2からSheet1への切り替えを行っています
この時点でOptionButtonのクラスの紐付けが完了しています
実際にOptionButtonを切り替えてみると、選択範囲の背景色がOptionButtonのONのものと同じに切り替わっています
OptionButtonが増えたとしても、問題は無いしコントロールの背景色を変更すれば変更後の色指定も合わせて行えます
Private Sub Worksheet_Deactivate()
Erase 動的作成
End Sub
また、このDeactivateイベントにより動的配列変数を解放しています
このイベントはシートがアクティブ状態でなくなったときに発生するイベントなので、別のシートに移動したら紐付けを解除する動きになります
状況によっては必要ないかもしれません
Private Sub CommandButton1_Click()
Selection.Interior.ColorIndex = 0
Erase 動的作成
End Sub
解放自体をユーザーにゆだねることも出来ます
その為にコマンドボタンを1つ作成しています
コマンドボタンをクリックすると、背景色を塗りつぶし無しに設定します
その後、動的配列変数を解放しています
画像の動きを確認してください
ボタンをクリックすることで、背景色が無くなり、Redのオプションボタンに切り替えても背景色は塗られることはありません
イベントが発生していないことが確認できます
動的配列でメモリ上に乗っかったままなので、基本的には必要ない時は解放してあげるほうが良いです
複数のコントロール
単一のコントロールだけであれば、全く問題にならないので普通にシートモジュールに作成すればいいです
また、コードによるコントロールの作成後の処理でも同様です
単一の作成後処理は以下の記事にあります
この記事でもありますが、複数のコントロールになるとこのクラスを使用する方法しかありません
クラス自体は、使用頻度が非常に低く無くても処理作成を行えることが多いため
初心者では踏み込みづらい内容かもしれません
なので、たくさんのコントロールのイベント処理を一括して管理するにはクラス使わなあかん、とだけ認識しておいてください