エクセルVBA 繰り返し処理を教えて下さい

管理台帳を作成しています。
初心者の為、永遠に長いコードを書いてしまっています。
下記コードを参考に、For　～　Nextなどのループ文コードをご教示願います。

内容
（リストボックスをダブルクリックし、登録メニュー（UserForm）のテキストボックスに転記）


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets("台帳").Activate
Worksheets("台帳").Range(Worksheets("台帳").Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 1), Worksheets("台帳").Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 11)).Select
With ListBox1
a = .ListIndex '選択した行番号を取得

登録メニュー.実施日TextBox.Text = .List(a, 1)
登録メニュー.団体名TextBox.Text = .List(a, 2)
登録メニュー.幹事名TextBox.Text = .List(a, 3)
登録メニュー.所属先TextBox.Text = .List(a, 4)
登録メニュー.役職TextBox.Text = .List(a, 5)
登録メニュー.郵便番号TextBox.Text = .List(a, 6)
登録メニュー.住所TextBox.Text = .List(a, 7)
登録メニュー.TELTextBox.Text = .List(a, 8)
登録メニュー.FAXTextBox.Text = .List(a, 9)
登録メニュー.携帯TextBox.Text = .List(a, 10)

登録メニュー.男性.Text = Cells(ActiveCell.Row, 11).Value
登録メニュー.女性.Text = Cells(ActiveCell.Row, 12).Value
登録メニュー.高校.Text = Cells(ActiveCell.Row, 13).Value
登録メニュー.中学.Text = Cells(ActiveCell.Row, 14).Value
登録メニュー.小学.Text = Cells(ActiveCell.Row, 15).Value
登録メニュー.園児.Text = Cells(ActiveCell.Row, 16).Value
登録メニュー.幼児.Text = Cells(ActiveCell.Row, 17).Value
登録メニュー.合計.Text = Cells(ActiveCell.Row, 18).Value
・
・
・
と、膨大な行が続いています。しまいには「プロシージャが大きすぎます」とエラーがでます。
テキストボックスはそれぞれ名前を変更してあります。

No.6 ベストアンサー 回答者： Qchan1962 回答日時： 2022/02/22 14:27

こんにちは

コードがすべてできているのなら、（エラー回避をするだけで良ければ）
プロシージャを分割すれば良いです。
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As Integer
Worksheets("台帳").Activate
Worksheets("台帳").Range(Worksheets("台帳").Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 1), _
Worksheets("台帳").Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 11)).Select
a = ListBox1.ListIndex '選択した行番号を取得

Call List_inTextBox(a)
Call Text_inCell
・
・
・

End Sub

'同じフォームモジュールに書いても問題ありません
Sub List_inTextBox(a As Integer)
登録メニュー.実施日TextBox.Text = .List(a, 1)
登録メニュー.団体名TextBox.Text = .List(a, 2)
登録メニュー.幹事名TextBox.Text = .List(a, 3)
登録メニュー.所属先TextBox.Text = .List(a, 4)
登録メニュー.役職TextBox.Text = .List(a, 5)
登録メニュー.郵便番号TextBox.Text = .List(a, 6)
登録メニュー.住所TextBox.Text = .List(a, 7)
登録メニュー.TELTextBox.Text = .List(a, 8)
登録メニュー.FAXTextBox.Text = .List(a, 9)
登録メニュー.携帯TextBox.Text = .List(a, 10)
End Sub

Sub Text_inCell()
登録メニュー.男性.Text = Cells(ActiveCell.Row, 11).Value
登録メニュー.女性.Text = Cells(ActiveCell.Row, 12).Value
登録メニュー.高校.Text = Cells(ActiveCell.Row, 13).Value
登録メニュー.中学.Text = Cells(ActiveCell.Row, 14).Value
登録メニュー.小学.Text = Cells(ActiveCell.Row, 15).Value
登録メニュー.園児.Text = Cells(ActiveCell.Row, 16).Value
登録メニュー.幼児.Text = Cells(ActiveCell.Row, 17).Value
登録メニュー.合計.Text = Cells(ActiveCell.Row, 18).Value
End Sub
・
・
・
ただ、色々と困る事がありそうです
＃２様、＃３様ご指摘のように書き直す方が良いと思います

以下はやっつけなので参考程度で
オブジェクト名を変更したり、タブインデックスを調整するのが面倒ならオブジェクト名をシートに書き出してしまう方法もありと思います。

新規シートや空き列（2列）を使ってコードを加工します
作業を行うシートは新規シートでWorksheets("WorkSet")としました
コードの　
登録メニュー.実施日TextBox.Text = .List(a, 1)　以下
登録最後までをコピー

空き列左（コードはA列にコピーしてB列に書き出す）　
下記を実行（標準モジュールに書きます）
Sub split_txt()
Dim r
For Each r In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
If r <> "" Then r.Offset(, 1) = Split(r, ".")(1)
Next
End Sub
B列に書き出された文字列がコントロール名ですので
B列を切り取ってA列に貼り付ける


ご質問のコードはこんな感じです
a = ListBox1.ListIndex '選択した行番号を取得

For i = 1 To 18
If i <= 10 Then 登録メニュー.Controls(Worksheets("WorkSet").Cells(i, 1) & "TextBox").Text = .List(a, i)
If i >= 11 And i <= 18 Then 登録メニュー.Controls(Worksheets("WorkSet").Cells(i, 1)).Text = Cells(ActiveCell.Row, i).Value
Next

カウント変数と対象インデックスが同じならまとめるのは比較的簡単

この回答へのお礼

ご返答が大変遅くなり申し訳ございません。大変参考になりました。

お礼日時：2022/03/31 12:08

No.5 回答者： fujillin 回答日時： 2022/02/22 14:22

こんにちは

UserFormは使わないので、簡略版でテストしてみただけですけれど・・

テキストボックスの名称を配列に定義しておいて、それを利用することでループすることは可能です。
他のプロシージャでも利用するようなら、共通変数として定義しておいても良いかもしれません。

全体像がよくわからないので、以下は要領のみです。
'// ボックス名の配列を作成（「Textbox」部分は共通なので省いています）
'// Splitで得られる配列は、添え字は0始まりになります。
　Dim boxName
　boxName = Split("実施日,団体名,幹事名,・・略・・", ",")

　With ListBox1
　　a = .ListIndex
'// 以下で、ループでテキストボックスに代入
　　For i = 1 To 10
　　　登録メニュー(boxName(i - 1) & "TextBox").Text = .List(a, i)
　　Next i
'　　・・・・
'　　・・・・
　End With

のような感じで可能と思います。

No.4 回答者： 初心者ですが何か。 回答日時： 2022/02/22 13:21

No.1です。

私なら無理にやるとしたらDictionaryへの登録をUserForm_Initialize()イベントでします。
コントロール名については使わないシートのセルに羅列しておくかな？
コードに記載しても文字数が多くなるかもですし。

No.3 回答者： 銀鱗 回答日時： 2022/02/22 13:15

どこかにリスト化して、そのリストを基に……という処理にした方が良いのかな。

そうすればリストのメンテナンスだけで検査項目の追加や修正が可能になります。

このやりかたなら、行番号や列番号を使って繰り返し処理が可能です。

この回答へのお礼

なるほどー。参考にさせていただきます。m(_ _)m

お礼日時：2022/02/22 15:44

No.2 回答者： よろずやkinchan 回答日時： 2022/02/22 13:09

＝の左右共に行番号のようなものが有るものでなければ繰り返し処理は難しいかと。

ムリクリForを使うのであれば
Dim ary
ary = array("","実施日","団体名","幹事名","所属先",<中略>"携帯")
Dim c as long
For c = 1 To UBound(ary)
登録メニュー.Controls(ary(c)&"TextBox").Text = .List(a, c)
Next
※未検証
みたいな感じにすることはできます。

No.1 回答者： 初心者ですが何か。 回答日時： 2022/02/22 12:49

正直無理に近いと思います。

For ~ To Next の場合、名前の後ろに連番（又は一定の間隔番号）があれば出来ますけど、今回のは完全にコントロール名を連続したものではないですよね？

例えば
『登録メニュー.実施日TextBox.Text』～は『登録メニュー.TextBox1.Text』～からってなら出来るという意味。
⇒『登録メニュー.男性.Text』とかが何かは不明ですけど。

なのでコントロール名を全て『連続するナンバーリングをつけたもの』に変えるしかないと思いますよ。
初級レベルな考えですけど。

この回答へのお礼

ありがとうございます。参考になります。m(_ _)m

お礼日時：2022/02/22 15:44