同じコンボボックス、リストボックスを使うには？

質問

よろしくお願いします。
今、ユーザーフォームを使って製造計画表を作っています。
コンボボックスで会社名、リストボックスで会社ごとの製品名を選択させるとこまで出来ました。
１日に３つの製品を作ることが出来るので、同じ中身のコンボボックス、リストボックスを使って３つ作りたいと思っています。
ユーザーフォームの形的にはこのような感じですが。
１．　コンボボックス　　リストボックス
２．　コンボボックス　　リストボックス
３．　コンボボックス　　リストボックス
今は、１．のとこだけは出来たのですが、２．３．は１．と同じコードをコピーして必要と思われるとこだけをコンボボックス２と変えたりしてみたのですが、上手くいきませんでした。こんなコードですが。
Private Sub UserForm_initialize()
'ComboBox1セット

Dim ico As Long
ico = 1
With ThisWorkbook.Worksheets("Sheet1")
Do While .Cells(1, ico) <> ""
Me.ComboBox1.AddItem .Cells(1, ico).Value
ico = ico + 1
Loop
End With
Me.ComboBox1.SetFocus
End Sub
Private Sub ComboBox1_Change()
'ListBox1セット
Dim ico As Long
'Me.ListBox1.Clear
ico = Me.ComboBox1.ListIndex + 1
With ThisWorkbook.Worksheets("Sheet1")
Me.ListBox1.List = .Range(.Cells(2, ico), _
.Cells(.Cells(Rows.Count, ico).End(xlUp).Row, ico)).Value
End With
End Sub

ＶＢＡも初めたばかりで質問の内容もわかりづらいとも思いますが、よろしくお願いします。

回答No.1

onlyrom · ベストアンサー

回答が付いてないようなので。。

UserForm Initializeイベントで３つのComboBoxにLIstをセット
ListBox1のChangeイベントが出来たら、それをコピーして、数値のところを、２，３に変えるだけです。

質問者のコードをそのまま使用するとして。。

'----- ３つのコンボにリストをセット--------
Private Sub UserForm_initialize()
　Dim ico As Long
　ico = 1
　With ThisWorkbook.Worksheets("Sheet1")
　　Do While .Cells(1, ico) <> ""
　　　Me.ComboBox1.AddItem .Cells(1, ico).Value
　　　ico = ico + 1
　　Loop
　End With

　Me.ComboBox2.List = Me.ComboBox1.List
　Me.ComboBox3.List = Me.ComboBox1.List

　Me.ComboBox1.SetFocus
End Sub

'----------ListBox1をセット----------------
Private Sub ComboBox1_Change()
　Dim ico As Long
　ico = Me.ComboBox1.ListIndex + 1
　With ThisWorkbook.Worksheets("Sheet1")
　　Me.ListBox1.List = .Range(.Cells(2, ico), _
　　.Cells(.Cells(Rows.Count, ico).End(xlUp).Row, ico)).Value
　End With
End Sub
'----------ListBox2をセット----------------
Private Sub ComboBox2_Change()
　Dim ico As Long
　ico = Me.ComboBox2.ListIndex + 1
　With ThisWorkbook.Worksheets("Sheet1")
　　Me.ListBox2.List = .Range(.Cells(2, ico), _
　　.Cells(.Cells(Rows.Count, ico).End(xlUp).Row, ico)).Value
　End With
End Sub
'----------ListBox３をセット----------------
Private Sub ComboBox3_Change()
　Dim ico As Long
　ico = Me.ComboBox3.ListIndex + 1
　With ThisWorkbook.Worksheets("Sheet1")
　　Me.ListBox3.List = .Range(.Cells(2, ico), _
　　.Cells(.Cells(Rows.Count, ico).End(xlUp).Row, ico)).Value
　End With
End Sub
'--------------------------------------------

ただこれではCombo,ListBoxが増えたときにコピーや修正が大変ですので、LIstBoxへのセットは殆ど同じですから、そこをサブルーチンにしてやります。

方法はサブルーチンをコールするときにComboBoxの番号を引数としてコールしてやります。

'-------------------------------
Private Sub ComboBox1_Change()
　Call ListBoxSet(1)
End Sub
'-------------------------------
Private Sub ComboBox2_Change()
　Call ListBoxSet(2)
End Sub
'-------------------------------
Private Sub ComboBox3_Change()
　Call ListBoxSet(3)
End Sub

'------共通ListBoxセット----------------
Private Sub ListBoxSet(No As Integer)
　Dim ico As Long
　ico = Me.Controls("ComboBox" & No).ListIndex + 1
　With ThisWorkbook.Worksheets("Sheet1")
　　Me.Controls("ListBox" & No).List = .Range(.Cells(2, ico), _
　　.Cells(.Cells(Rows.Count, ico).End(xlUp).Row, ico)).Value
　End With
End Sub
'--------------------------------------

以上。

お礼

onlyromさん、ありがとうございました。自分の想像してた通りの物が作ることができました(^_^)
とても助かりました。

お礼の返事がだいぶ遅れてしまってすいませんでした。