VBAのフォームでTextBoxがいっぱいある時

質問

Microsoft Excel 2000 for VBAのフォーム機能を使用して
TextBox？に値が入力したらシートの指定したセルへ値が入るようにしたいんですが、
TextBoxがいっぱいあるため、以下のように非常に長いプログラムになってしまいました。

Private Sub TextBox1_Change()
Sheets(sheetname).Cells(1, 横位置).Value = TextBox1.Value
End Sub
TextBox2～29は繰り返し
Private Sub TextBox30_Change()
Sheets(sheetname).Cells(30, 横位置).Value = TextBox30.Value
End Sub

上手く配列化なんかでまとめる方法がありましたらアドバイスください。

回答No.2

nishi6 · ベストアンサー

実質、数行なので説明といっても何を書けばいいのか・・・

●最初に・・・
VBAではVBと違い、コントロール配列の機能はありません。
しかし、コントロールのオブジェクト名をOptionButton1、OptionButton2、OptionButton3の
ようにすると、ユーザーフォームのオブジェクト名を　.Controls("OptionButton" & i) で扱え、
あたかもコントロール配列のように使えます。CheckBoxでもTextBoxでも同じです。

例えば、CommandButton1 を押すと、OptionButton1からOptionButton31までの31個のValueを
判定することが For Next や For Each でできることになります。これで外部から多数のコント
ロールを一括して参照できることになります。
参照できれば、各テキストボックスの値を合計したり、検証したり、コントロールの初期化を
スマート（一括で、短く）にできたりします。

ただし、質問のケースは上と違い、多数のTextBoxのChangeイベントをコントロール配列風に
したいということで、多数のコントロール内部からのイベントになるわけで一工夫必要になります。

●まず、クラスモジュールの次の２行は ・・・

　　(C1)Private WithEvents myText As MSForms.TextBox
　　(C2)Private myIndex As Integer

　(C1)でTextBox型の変数をWithEventsを使って宣言しています。
　これでこの変数は(テキストボックス)のChangeイベントを検知できるようになります。
　(C2)でInteger型の変数を宣言しています。
　このmyIndexがTextBox1、TextBox2等の番号にあたる箇所で、TextBox型の変数を配列と
　したときのインデックスになります。

●次に(C3)部分で、TextBox型の変数myTextが参照するTextBoxを定義する関数を書いています。
　後の、UserForm_Initializeでこの関数が呼ばれ、myIndexで制御できるTextBox型の変数が
　作成されるわけです。

　　(C3)Public Sub S_setText(NewText As MSForms.TextBox, Index As Integer)
　　(C3)　　Set myText = NewText
　　(C3)　　myIndex = Index
　　(C3)End Sub

●次に(C4)部分で、TextBox型の変数myTextのChangeイベントを定義しています。
　myIndexがあるため、コントロール配列風に扱え、コードはこれだけで済みます。

　　(C4)Private Sub myText_Change()
　　(C4)　　With UserForm1
　　(C4)　　　　Worksheets("Sheet1").Cells(myIndex, 横位置) = Val(.Controls("TextBox" & myIndex))
　　(C4)　　End With
　　(C4)End Sub

●質問に『横位置』とあったので、ここでこの変数を有効にするため、
　とりあえず、標準モジュールに

　　(M1)Public Const 横位置 = 1　　　'A列

を記述しています。

●最後にユーザーフォームのコードです。

　(F1)でクラスで定義したTextBox型の変数が30個いるので配列に定義しています。

　　(F1)Private myTextArray(1 To 30) As New Class1

　(F2)ではUserForm_Initializeイベントで、ユーザーフォームが呼び出された時に
　クラスのTextBox型の変数が各テキストボックスを参照できるようにクラス内で定義したS_setTextパブリック
　関数を呼び出しています。これで30個のテキストボックスのChangeイベントをひとつのイベントプロシージャ
　で書けるようになります。

　　(F2)Private Sub UserForm_Initialize()
　　(F2)　　Dim i As Integer
　　(F2)　　For i = 1 To 30
　　(F2)　　　　myTextArray(i).S_setText UserForm1.Controls("TextBox" & i), i
　　(F2)　　Next
　　(F2)End Sub

説明は作るより難しいですね。
余り理論的ではなく、書いているコードの説明かもしれません。
クラスについて書かれた本も見かけるようになってきたので、その辺から調べてみてはどうでしょうか。

お礼

お忙しい中ありがとうございます。
クラス等の意味をちゃんと勉強したいと思います。
また何かありましたらアドバイスお願いします。

回答No.1

nishi6

ユーザーフォーム上にTextBox1からTextBox30があり、質問にあるように、コードを30個書かないでChangeイベントを拾えるようにしてみました。

Excel-VBAはVBと違い、コントロール配列の機能がないので、クラスでWithEventsを使ってイベントを拾い、あたかもコントロール配列のようにしてみました。こんな使い方もあるという例です。

テキストボックスの値を書き込むシートのシート名は『Sheet1』としています。ご参考に。(実際は5個のコントロールでしかチェックしていません。30個も同じでしょう。Excel2000です)

ユーザーフォームのコードウインドウに貼り付け(ユーザーフォーム=UserForm1)
↓
Private myTextArray(1 To 30) As New Class1

Private Sub UserForm_Initialize()
　　Dim i As Integer

　　For i = 1 To 30
　　　　myTextArray(i).S_setText UserForm1.Controls("TextBox" & i), i
　　Next
End Sub

クラスモジュールを挿入して貼り付け(クラスモジュール=Class1)
↓
Private WithEvents myText As MSForms.TextBox
Private myIndex As Integer

Public Sub S_setText(NewText As MSForms.TextBox, Index As Integer)
　　Set myText = NewText
　　myIndex = Index
End Sub

Private Sub myText_Change()
　　With UserForm1
　　　　Worksheets("Sheet1").Cells(myIndex, 横位置) = Val(.Controls("TextBox" & myIndex))
　　End With
End Sub

標準モジュールを挿入して貼り付け
↓
Public Const 横位置 = 1　　　'A列

お礼

早速のお返事ありがとうございます。
nishi6さんが作っていただきました物で
私の要求は十分満たす内容になっておりました。
どうもありがとうございました。
でも私の知識不足のため、今一構造が理解できない・・・。
できればもう少し詳しい解説をお願いできないでしょうか？