ExcelvbaでActiveコントロールのコンボボックス名の指定に繰り返し変数を使いたい

Excel2013の"設定"シート上にActiveコントロールのコンボボックスを配置しています。
それと併せて、ユーザーフォーム上にもコンボボックスを配置しています。
"設定"シート上のコンボボックスの値が"○"と"×"が設定されていたとすると、ユーザーフォームを呼び出した時に同じ設定で表示させたいと考えております。

一応実現できているのですが、10個くらいあるので、まとめた書き方ができないものか？と考えています。
現在書いているのを記載します。

For i = 1 To 10
With Me.Controls("ComboBox" & i)
.Clear
.AddItem "○"
.AddItem "×"
'本当はここで、If ThisWorkbook.Worksheets("設定").Controls("ComboBox" & i).Value = "○" Then・・・みたいに処理できればよいのですがエラーになる
End With
Next i
With ThisWorkbook.Worksheets("設定")
If .ComboBox1.Value = "○" Then
Me.ComboBox1.ListIndex = 0
Else
Me.ComboBox1.ListIndex = 1
End If
If .ComboBox2.Value = "○" Then
Me.ComboBox2.ListIndex = 0
Else
Me.ComboBox2.ListIndex = 1
End If
If .ComboBox3.Value = "○" Then
・
・
・このようなパターンで続いていく・・・

シート上にある複数のActiveコントロールのコンボボックスの指定を、ユーザーフォームのコンボボックスを指定するように、
If ThisWorkbook.Worksheets("設定").Controls("ComboBox" & i).Value
のように繰り返し変数で指定したいのですが、うまくいきません。
もし、他に書き方があるならばと思っています。

No.2 ベストアンサー 回答者： bonaron 回答日時： 2020/05/29 17:40

こんな感じで。

Dim i As Integer
Dim ctl As Control
For i = 1 To 10
　　Set ctl = Me.Controls("Combobox" & i)
　　With ThisWorkbook.Worksheets("設定").OLEObjects(ctl.Name).Object
　　　　ctl.List = .List
　　　　ctl.Value = .Value
　　End With
Next

この回答へのお礼

ありがとうございました。

何故か.Listだとi=4の所でエラーになってしまいました。特に3までと違いはないし、存在もしていたのですが・・・。
そこで、.ListIndexに変更すると取得できました。イミディエイトで見ても、.Listだと型が一致しないエラーになったのですが、.ListIndexに変更すると値が返ってきたので.ListIndexが正解なんでしょうかね。

そこを変更したら、思い通りになりました。

お礼日時：2020/05/29 20:22

No.3 回答者： Qchan1962 回答日時： 2020/05/29 22:47

＃１です。

>"シート上にActiveコントロールのコンボボックス
>それと併せて、ユーザーフォーム上にもコンボボックスを配置しています。
敢えて標準モジュールでのサンプルを示しましたが、理解に至っていないようですかね。
＞シート上にある複数のActiveコントロールのコンボボックスの指定を
どのコントロールのインデックスを拾っているか確認したほうが良いかと思いますが
静観します。

この回答へのお礼

理解に至っていない所もあるかと思いますが、とりあえず思い通りになり完了です。
ありがとうございました。

お礼日時：2020/05/30 00:30

No.1 回答者： Qchan1962 回答日時： 2020/05/29 17:39

こんにちは、

＞シート上にActiveコントロールのコンボボックス
変数　i　の型をStringに変えないといけないかと思います。

参考に成りますでしょうか？
標準モジュールで
Sub hoge()
　Dim objOLE, i As Long
　Dim CtrlName
　i = 1
　CtrlName = "ComboBox" & CStr(i)
　Set objOLE = Worksheets("設定").OLEObjects(CtrlName)
MsgBox ("エラーではありません")
End Sub

iをループ変数で使用すればOKかと

この回答へのお礼

ありがとうございました。

お礼日時：2020/05/29 20:17