質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.48%

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 5,783

score 5

初めまして。
現在、Excelのユーザーフォームにてcombobox1とcombobox2を連動させようと試行錯誤しているのですが、求めている内容がcombobox2に表示されません。combobox1にadditemメソッドで表示させて、combobox1に追加された理学療法士、作業療法士、言語聴覚士が選択された時に、combobox2に利用者名が表示されるようにしたいのですが、下記に記載してコード(①)を使用すると−1と表示されます。さらに繰り返し選択をすると−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



イメージ説明

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+2

コンボボックスでリストにアイテムを追加するときは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起点)。それを利用して列位置を計算してます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/06 10:18

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

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

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

    キャンセル

  • 2019/11/06 10:26

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

    キャンセル

  • 2019/11/06 22:47

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

▼ 「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


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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/05 22:42

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

    キャンセル

-2

Select Case a

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.48%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る