Excel VBEでActiveXコントロールの値を効率的に取得する方法

質問

エクセルシート状にActiveXコントロールのチェックボックス、テキストボックスを多数配したアンケートを作成しています。

＜イメージ＞
問題文
□回答候補１
□回答候補２
□回答候補３
自由記入欄
（テキストボックス）
※四角の部分が複数回答可のチェックボックスになっており、テキストボックスに自由記入をしてもらう形です。

複数人でアンケートを作成、改良しているため、追加や削除を加える必要から、オブジェクト名を任意に設定しています。
たとえば「TextBox11001」「CheckBox22010」といった形なのですが、問１の小問１の選択肢１、問２の小問２の選択肢10という意味であるため、番号は飛び飛びです。

電子データを配布し、回収後、全員分のデータを一覧表にするマクロを作成しています。
設問ごとに、チェックがあれば、1、なければ0、テキストはそのまま転記という形です。

（イメージ）
名前　問1-1-1　問1-1-2　問1-1-3　問1-2-1　問1-2-2　問1自由…
○○　　　1　　　　　0　　　　　1　　　　0　　　　1　コメント
△△　　　1　　　　　1　　　　　0　　　　1　　　　0　コメント

集計表の見出しは設問が割り当てられており、
チェックボックス、テキストボックスがアンケートの順番に全て一行に並ぶように作っています。

開いた記入済みアンケートを次々と開いて（この部分は省略）のデータ（シート１～３がSht1～3、また集計用ブックにある集計シートがShtAと定義してある）を所定のセルへ転記するものです。

ShtA.Range("D14").Value = Sht3.TextBox321001.Text
ShtA.Range("F14").Value = Sht3.TextBox321002.Text
ShtA.Range("H14").Value = Sht3.TextBox321003.Text
ShtA.Range("J14").Value = Sht3.TextBox321004.Text
ShtA.Range("L14").Value = Sht3.TextBox321005.Text
ShtA.Range("N14").Value = Sht3.TextBox321006.Text
ShtA.Range("P14").Value = Sht3.TextBox321007.Text
ShtA.Range("Q14").Value = Sht3.TextBox3210.Text

If Sht1.CheckBox110101.Value = True Then
ShtA.Range("C4").Value = 1
Else
ShtA.Range("C4").Value = 0
End If

If Sht1.CheckBox110102.Value = True Then
ShtA.Range("D4").Value = 1
Else
ShtA.Range("D4").Value = 0
End If

If Sht1.CheckBox110201.Value = True Then
ShtA.Range("G4").Value = 1
Else
ShtA.Range("G4").Value = 0
End If

全問コピー＆ペーストなどで、セル番地だけ入れ替えて作ったところ、作業自体はうまくできました。
しかし、設問や選択肢が多いため、同じような記述が300個ほど続いてしまい、非常に煩雑です。
もっと効率的に書く方法はないでしょうか。

回答No.3

onlyrom

こういった場合は、集計セルとコントロールの対応表を作成しそれを利用するのがベターでしょう。
設問の追加削除、コントロール名の変更などにもVBAコード自体に手を加えることなく簡単に対応できると思いますが。。。

集計ブックに対応表シートを作成する。
例えば、以下のように。

集計セル＿＿＿コントロールのあるシート名＿＿＿コントロール名＿＿
＿Ｃ４＿＿＿＿＿＿＿Ｓｈｅｅｔ１＿＿＿＿＿＿＿＿TextBox1101＿＿
＿Ｄ４＿＿＿＿＿＿＿Ｓｈｅｅｔ１＿＿＿＿＿＿＿＿CheckBox1102＿＿
＿Ｅ４＿＿＿＿＿＿＿Ｓｈｅｅｔ２＿＿＿＿＿＿＿＿TextBox2101＿＿
＿Ｆ４＿＿＿＿＿＿＿Ｓｈｅｅｔ２＿＿＿＿＿＿＿＿TextBox3102＿＿
　　　　　・・・・・・
　　　　　・・・・・・

以上。

回答No.2

xls88

繰り返しのパターン（規則性）を見つけて、ループすればよいと思います。

>ShtA.Range("D14").Value = Sht3.TextBox321001.Text
>ShtA.Range("F14").Value = Sht3.TextBox321002.Text
>ShtA.Range("H14").Value = Sht3.TextBox321003.Text
この部分、列は1列置きで、TextBoxのインデックスは連続のようですから

Dim i As Long
Dim j As Long
For i = 4 To 18 Step 2
　　j = j + 1
　　ShtA.Cells(1, i).Value = Sht3.OLEObjects("TextBox" & 321000 + j).Object.Value
Next

というように出来ると思います。

>If Sht1.CheckBox110101.Value = True Then
>ShtA.Range("C4").Value = 1
>Else
>ShtA.Range("C4").Value = 0
>End If
こちらは、CheckBoxのインデックスは連番のようですが
値の転記先Rangeの列番号にはどのような規則性があるか分かりますか？
セル番地が繰り返されるパターンがどうなっているのか、実際例を提示してみてください。

回答No.1

KonnaMonde

>同じような記述が300個ほど続いて

とは
　　「ShtA.Range("D14").Value = Sht3.TextBox321001.Text」
の文中の「Range("D14")」「TextBox321001」　のことですか？

アンケートの種類は何種類くらいですか？

問題文の「回答候補」は３つあるのに、IF文では「TRUE」「ELSE」の2つに絞られている理由は？　「回答候補１」～「回答候補３」　の３つの評価はどこに表示されるのですか？

「問1-1-1　問1-1-2　～　」　の欄の個数はいくつですか？

IF文の「ShtA.Range("C4").Value　～　ShtA.Range("G4").Value」 と　「ShtA.Range("D14").Value = Sht3.TextBox321001.Text　～　ShtA.Range("Q14").Value = Sht3.TextBox3210.Text」との関連は？

>所定のセルへ転記

の「所定のセル」はどのセルを指すのですか？

何度読み返しても全体像が見えて来ない(概念は理解できる)のは、私だけですかね？

何も知らない相手に分かるように、具体的にどうしたいのかの説明がなされないと、回答したくてもできませんよね。