ExcelVBAでControlオブジェクトのChangeイベントを一括制御する方法

コントロールオブジェクトがたくさんあったとして、それの Change イベント、全部ベタで書かなきゃいけないの…？と思って調べてみたら、とっても便利な方法があったのでシェアさせて頂きます。

コントロールの変数制御

過去に、コントロールオブジェクト自体の制御については書いたことがありました。

• ExcelVBAでTextBoxなどのControlオブジェクトを変数制御する

TextBox1, TextBox2, TextBox3 のような数値の部分を変数で制御すると、For～Nextなどで回せるようになって、ベタ打ちするよりずっと楽になるなーと思って重宝していたんですが。

Changeイベントを楽にできないものか…

今回、TextBox から内容が変わったときに走る、いわゆる Change イベントを全ての TextBox に実装したかったのですが、TextBox の数が多くて「これ全部に Change イベント書くのか…」とげんなりして、なんかスマートにできないかなーと調べてみたらこんな記事が。

• VBAのフォームでTextBoxがいっぱいある時 | Office系ソフトのQ&A【OKWave】

できるのか！！すごい！参考にさせて頂いて実装してみたらすっごく省コードで出来ちゃいました！nishi6さんに、この場にて厚く御礼申し上げます。

せっかくなので、参考にさせていただいて、私なりにちょびっと変えたほうのコードも載せておこうと思います。一応解説も私なりに書いてみましたが、元記事のほうもとても参考になりますので、そちらも是非ご参照ください。

コード

Class1

まず、[挿入]→[クラスモジュール]。Class1に以下を書きます。

Private WithEvents Target As MSForms.TextBox
 
Public Sub SetCtrl(new_ctrl As MSForms.TextBox)
  Set Target = new_ctrl
End Sub
 
Private Sub Target_Change()
  If Target.Value = "" Then
    Target.BackColor = RGB(200, 200, 200) '背景を灰色に
  Else
    Target.BackColor = RGB(255, 255, 255) '背景を白に
  End If
End Sub

1行目のWithEventsでイベントを拾うことができるので、TextBox 型で宣言しておきます。

3～5行目は、Targetとなる TextBox をセットしています。

7～13行目が、実際に処理を行う Change イベントです。このコードでは変更のあった TextBox の中身が空なら背景を灰色、そうでなければ白にしています。

UserForm1

次に、対象のTextBoxがおいてあるユーザーフォームへ。

Private ctrl(1 To 30) As New Class1
 
Private Sub UserForm_Initialize()
  Dim i As Integer
  For i = LBound(ctrl) To UBound(ctrl)
    ctrl(i).SetCtrl Me("TextBox" & i)
  Next
End Sub

ユーザーフォームが呼び出されたとき、先程の3～5行目に書いた SetCtrl プロシージャを呼び出して、対象の TextBox をそれぞれ Target へ定義します。

これなら、TextBox がたくさんあっても、指定の番号のものだけ Change イベントを一括で制御できちゃいます。素敵だ！

チェックボックスでもできちゃう！

ちょこっと書き換えれば、CheckBox の Click イベントにも。

Class1

Private WithEvents Target As MSForms.CheckBox
 
Public Sub SetCtrl(new_ctrl As MSForms.CheckBox)
  Set Target = new_ctrl
End Sub
 
Private Sub Target_Click()
  If Target.Value = True Then
    'チェックボックスがオンのとき
  Else
    'チェックボックスがオフのとき
  End If
End Sub

UserForm1

Private ctrl(1 To 30) As New Class1

Private Sub UserForm_Initialize()
  Dim i As Integer
  For i = LBound(ctrl) To UBound(ctrl)
    ctrl(i).SetCtrl Me("CheckBox" & i)
  Next
End Sub

同じ感じにすれば、他のイベントやコントロールでもできそうですね！

ただ、WithEvents は Enter, Exit, BeforeUpdate, AfterUpdate など、コントロールのフォーカス移動時に発生するイベントは検知できないみたいですのでご注意を。

• WithEventsでイベントを拾う – EXCEL-LENCE webhttp://www.excellenceweb.net/vba/class/userform_hook.html

でもすごく便利ですね。興奮してしまいましたw

追記：別解

上記では配列を使ってコントロールの番号を指定していますが、ユーザーフォーム上の全てのコントロールをループして、その中の任意のタイプのコントロールだけ、ということもできます。

Class1への記述はそのままで、ユーザーフォーム側だけ変えます。

UserForm1

Private CheckCollection As Collection 'コレクションの宣言

Private Sub UserForm_Initialize() 'Formが開くとき
  Set CheckCollection = New Collection 'コレクション生成
  Dim ctrl As Control 'コントロール変数の宣言
  Dim obj As Class1 'インスタンス変数を宣言
  For Each ctrl In Me.Controls  'フォーム内のコントロールをループ
    If TypeName(ctrl) = "CheckBox" Then 'コントロールのタイプがチェックボックスだったら
      Set obj = New Class1 'インスタンスの生成
      obj.SetCtrl ctrl 'コントロールをセット
      CheckCollection.Add obj 'コレクションへ追加
      Set obj = Nothing 'インスタンス破棄
    End If
  Next
End Sub

番号関係なく全部のコントロールに対応させたい、という場合はこちらの使い方でも。

関連

• ExcelVBAでカレンダーコントロールを自作する

この WithEvent を使って、クリックイベントを拾うカレンダーコントロールを作ってみました。

• ExcelVBAのクラスモジュールって何？という人向けの使い方まとめ

クラスモジュールの使い方をまとめてみました。