Excel VBA ユーザーフォームのコンボボックス１、２を連動させる

Question

初めまして。
現在、Excelのユーザーフォームにてcombobox1とcombobox2を連動させようと試行錯誤しているのですが、求めている内容がcombobox2に表示されません。combobox1にadditemメソッドで表示させて、combobox1に追加された理学療法士、作業療法士、言語聴覚士が選択された時に、combobox2に利用者名が表示されるようにしたいのですが、下記に記載してコード（①）を使用すると−1と表示されます。さらに繰り返し選択をすると−１が増えてきます（②）。利用者を取ってくるsheetsは（③）に添付しました。利用者は利用者が増えるごとに選択範囲を広げたり、縮めたりしたいと考えております。さらに考えている事としましては、利用者名が選択されたら、他のsheetsに利用者が担当している担当者を一覧で表示できるように考えております。
色々なサイトから引っ張ってきた内容ばかりで、自分でもなんとなくでしか理解していない内容も含まれています。
質問内容も分かりにくくなってしまい、大変申し訳ございませんが、必要コード、修正、アドバイスなどお願い致します。

①

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem ""
        .AddItem "理学療法士"
        .AddItem "作業療法士"
        .AddItem "言語聴覚士"
    End With
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーー
Private Sub ComboBox1_change()
Dim a As String
Dim LASTROW1 As Long
Dim LASTROW2 As Long
Dim LASTROW3 As Long
a = ComboBox1
LASTROW1 = Cells(Rows.Count, 3).End(xlUp).Row
LASTROW2 = Cells(Rows.Count, 7).End(xlUp).Row
LASTROW3 = Cells(Rows.Count, 11).End(xlUp).Row

With UserForm1
            .ComboBox2.Text = ""
            
Select Case a
Case "理学療法士"
    ComboBox2.AddItem Worksheets("療法士管理").Range(Cells(5, 3), Cells(LASTROW1, 3)).Select
    
Case "作業療法士"
    Me.ComboBox2.AddItem Worksheets("療法士管理").Range(Cells(5, 7), Cells(LASTROW2, 7)).Select
    

Case "言語聴覚士"
    Me.ComboBox2.AddItem Worksheets("療法士管理").Range(Cells(5, 11), Cells(LASTROW2, 11)).Select
    
End Select
End With
End Sub

②

Answer 1

コンボボックスでリストにアイテムを追加するときはAddItemでは一つずつしか追加でいませんので、他の方の回答のようにループ処理で一つずつ追加していくことになります。

セル範囲をデータを表示させる場合は、Listプロパティ、またはRowSourceプロパティを利用すると一度に追加できます。

Listプロパティを使った場合のコード例

Private Sub UserForm_Initialize()
    With Worksheets("療法士管理")
        ComboBox1.List = Array(.Range("B5").Value, .Range("F5").Value, .Range("J5").Value)
    End With
End Sub

Private Sub ComboBox1_AfterUpdate()
    Dim col As Long
    col = ComboBox1.ListIndex * 4 + 3
    If col < 0 Then
        ComboBox2.Clear
        Exit Sub
    End If
    With Worksheets("療法士管理")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
        ComboBox2.List = .Range(.Cells(5, col), .Cells(lastRow, col)).Value
        ComboBox2.Text = ""
    End With
End Sub

Changeイベントだと一文字入力したり削除しただけで発生するのでAfterUpdateイベントを使いました。
Listプロパティには配列を代入できます。セル範囲.Valueで配列になります。
ListIndexプロパティで何番目が選択されたか取得できます（0起点）。それを利用して列位置を計算してます。

Comments

[Yokubari_PT]

丁寧なご説明までありがとうございます。
行えました。ありがとうございます。

[Yokubari_PT]

ちなみにシートがactiveじゃないと
ComboBox2.List = .Range(Cells(5, col), Cells(lastRow, col)).Value
でデバックになります。何故ですか？

[hatena19]

あっ、すみません。. がぬてけました。
ComboBox2.List = .Range(.Cells(5, col), .Cells(lastRow, col)).Value
に変更してください。
回答の方も修正しておきました。

[Yokubari_PT]

withステートメントに必要な . が抜けてたわけですね。分かりました。
何度も申し訳ないのですが、
Dim col As Long
col = ComboBox1.ListIndex * 4 + 3
If col < 0 Then

はDimでcol変数を定義して、colnの変数はセルの値を求めているんだと思うのですが、　＊４＋３の意味が分からないのですが…。また If col < 0 Thenはcolが０未満であればと言うのは、空白であればを示しているのですか？

お手数かけますがお願い致します。

[hatena19]

ComboBox1.ListIndex は何番目が選択されているかが取得できます。ただし、一番目が0、二番目か1となります。また、選択されていない場合は -1 になります。
一番目が選択されている場合は、0 * 4 + 3 の演算で結果は 3 になります。
二番目が選択されている場合は、1 * 4 + 3 の演算で結果は 7 になります。
つまり、リストデータのある列を計算でだしています。
非選択の場合は、-1*4+3 で演算結果は -1 になるので、If col < 0 Then は非選択の場合という意味になります。

[Yokubari_PT]

ご回答ありがとうございます。詳しくわかりました。
汎用的に使用させて頂きます。

Answer 2

コンボボックスに要素を入れるときは、
１つずつしか追加することはできません。
取得したせる範囲の内容を1つずつ追加するとうまく動きます。
また、コンボボックス２に要素を追加する前に
要素を初期化する仕様が入ってなかったです。

▼ 「ComboBox1_Change」関数の内容を使わない変数を削除してスッキリ修正しました。動作確認済みです

Private Sub ComboBox1_Change()
    Dim i_row As Integer
    Dim i_col As Integer
    
    With UserForm1
        .ComboBox2.Text = ""
        .ComboBox2.Clear
    
        Select Case ComboBox1
            Case "理学療法士"
                i_col = 3
            Case "作業療法士"
                i_col = 7
            Case "言語聴覚士"
                i_col = 11
            Case ""
                Exit Sub
        End Select
        
        For i_row = 5 To Cells(Rows.Count, i_col).End(xlUp).Row
            ComboBox2.AddItem Worksheets("療法士管理").Cells(i_row, i_col).Value
        Next
    End With
End Sub

ご参考にしてみてください！

Comments

[Yokubari_PT]

Dim i_col As Integer はどこで変数を定義しているのですか？

Answer 3

Private Sub ComboBox1_change()を変えました。
以下のようにします。
AddItemは一度に全利用者を追加することはできません。地道に1名ずつ追加します。
職種によりどの列の利用者を追加するかが変わるので、サブプロシージャを作って、そこで追加するように
しました。追加の前に一旦、コンボボックスを全てクリアしておくことも必要です。

Private Sub ComboBox1_change()
    Dim a As String
    a = ComboBox1.Text
    Select Case a
    Case "理学療法士"
        Call 利用者氏名設定(3)
    Case "作業療法士"
        Call 利用者氏名設定(7)
    Case "言語聴覚士"
        Call 利用者氏名設定(11)
    End Select
End Sub


Private Sub 利用者氏名設定(ByVal col As Long)
    Dim lastrow As Long
    Dim wrow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("療法士管理")
    lastrow = ws.Cells(Rows.Count, col).End(xlUp).Row
    ComboBox2.Clear
    For wrow = 5 To lastrow
        ComboBox2.AddItem ws.Cells(wrow, col).Value
    Next
End Sub

Answer 4

Select Case a

ここで実行を止めて、aになにがはいってるのかチェックしてみよう