コンボボックスの変更に応じてテキストボックスの有効を切り替える(ExcelVBA2007)

質問

お世話になります。
Excel VBA 2007で質問です。

今、
　Label1　ComboBox1　TextBox1
　Label2　ComboBox2　TextBox2
・・・
　Label100　ComboBox100　TextBox100
のようなフォームがあるとします。
ComboBox1～100は「Any」「is」「is not」のいずれかの値が取れ、初期値はAnyとします。
TextBox の Enabled プロパティの初期値は False とします。
やりたいことは、
ComboBox が「Any」でない場合（is または is not のいずれかの場合）にのみその右隣の TextBox の Enabled プロパティを True にしたいのです。

いま、ComboBox30 を Any から is に切り替えたとします。
すると、TextBox30.Enabled を False から True にしたいのです。

ここで、ComboBox30 を is から is not に切り替えたとします。
TextBox30.Enabled は True のままです。

ここで、ComboBox30 を is not から Any に切り替えたとします。
すると、TextBox30.Enabled を True から False にしたいのです。

ComboBox のイベントハンドラで

Sub ComboBox30_Change ()
　If ComboBox30.Value = "Any" Then
　　TextBox30.Enabled = "False"
　Else
　　TextBox30.Enabled = "True"
　End If
End Sub

のように書けばいいことは分かっているのですが、その場合は数字だけ変えたイベントハンドラを100個書かなければなりません。

このイベントハンドラを１個にすることはできるでしょうか。
（コンボボックス100個のうちどれかが変更されたら、変更されたコンボボックスの番号を知りながら呼び出されるハンドラ）

次善の策として、

Sub ComboBox30_Change ()
　userSubroutine
End Sub

のように書くこともできるかと思いますが、この場合、どのコンボボックスが変更されたか（どのイベントハンドラが起動されたか）を知って、userSubroutine に渡さなければなりません。

よろしくお願いします！

回答No.1

masa_019 · ベストアンサー

こんにちは。

クラスモジュールを挿入して、そこに

Option Explicit
Public WithEvents CBX As MSForms.ComboBox
Public idx As Integer

Private Sub CBX_Change()

If CBX.Value = "Any" Then
UserForm1("TextBox" & idx).Enabled = False
Else
UserForm1("TextBox" & idx).Enabled = True
End If

End Sub

フォームモジュールには、

Option Explicit
Private cls(1 To 100) As New Class1

Private Sub UserForm_Initialize()

Dim i As Integer

For i = 1 To 100
Set cls(i).CBX = Me.Controls("ComboBox" & i)
cls(i).idx = i
cls(i).CBX.List = Array("Any", "is", "is not")
Next

End Sub

Private Sub UserForm_Terminate()

Dim i As Integer
For i = 1 To 100
Set cls(i).CBX = Nothing
Next

End Sub

当方、EXCEL2002ですが、2007でもいけるでしょう。

お礼

ありがとうございます！
週明けに作業し次第、ご報告させていただきます。

補足

ばっちりでした！
ありがとうございます。