[[20150423143424]]　『コンボボックスの連携』（yuuki11）　ページの最後に飛ぶ

『コンボボックスの連携』（yuuki11）

VBA初心者です。コンボボックスの絞り込みの使い方について教えてください。
ユーザーフォームのコンボボックス1に都道府県を表示させ、選択した都道府県の市をコンボボックス2に表示させたいのですがどうすれば良いでしょうか。都道府県などの情報は別ブック（データ.xlsx）のJ列（都道府県）とK列（市）に入っています。それを選択して、出てきたデータをリストボックス1に表示させたいのですが……。過去の質問などを漁ってみたのですが、

A列　　　B列　　C列　　…
北海道　札幌　青森
青森　　函館　八戸
岩手　　千歳　弘前

こういう風に、市別に分かれておらず、

　　J列　　　K列
４　北海道　札幌
５　北海道　函館
６　青森　　弘前
・・・

という風に、K列にいろんな市が入り混じっている状態です。この場合、どのようにして検索をかけたら良いのでしょうか。コンボボックス1で選択した都道府県名を元に、J列でVLOOKUPを使って…と一度は考えたのですが、一行しか取り出せず、また振り出しに戻ってしまいました。

同じワークシートではは出来るんですが、これを別ブックから、しかもいろんな市が入り混じってる状態となるとうまくいきません。。

Worksheets("Sheet1").Select
For i=1 To Cells(Rows.Count, 1).End(xlUp).Row
i=1
ComboBox1.AddItem Cells(i, 1).Value
Next i
End Sub

Private Sub ComboBox1_Change()
ComboBox2.Clear
Worksheets("Sheet1").Select
myCol = ComboBox1.ListIndex + 2
For i=1 To Cells(Rows.Count, myCol).End(xlUp).Row
ComboBox2.AddItem Cells(i, myCol).Value
Next
End Sub

ご存知の方、どうか宜しくお願いします。

＜使用アプリ：、使用 OS：Windows7 ＞

別ブックにデータがあると、データを使いたいときは2つのブックを開いておく必要があるので、使いにくいですよ？
県と市なんて滅多に変わるものでもないので、シートをコピーして、同じブック内に置いてはいかがですか。

 Private Sub UserForm_Initialize()
    Dim i As Long

    With Sheets("Sheet2")
        For i = 1 To .Cells(.Rows.Count, "J").End(xlUp).Row
            ComboBox1.Text = .Cells(i, "J").Value
            If ComboBox1.ListIndex < 0 Then
                ComboBox1.AddItem .Cells(i, "J").Value
            End If
        Next i
    End With
 End Sub

 Private Sub ComboBox1_Change()
    Dim i As Long

    ComboBox2.Clear

    With Sheets("Sheet2")
        For i = 1 To .Cells(.Rows.Count, "J").End(xlUp).Row
            If ComboBox1.Text = .Cells(i, "J").Value Then
                ComboBox2.Text = .Cells(i, "K").Value
                If ComboBox2.ListIndex < 0 Then
                    ComboBox2.AddItem .Cells(i, "K").Value
                End If
            End If
        Next i
    End With
 End Sub
（???） 2015/04/23(木) 15:08

回答ありがとうございます。
別ブックにあるデータは、そのブックをいじったり動かしたりすることができないのです…。
それから、県と市と書いているのは一番わかりやすいと思ったからで、実際は別のデータが入っています。Ｊ列は人の名前が入っており、まず変わることはないと思うのですが、Ｋ列は日付なのでどんどん増えていきます。わかりづらくて申し訳ありません。
（yuuki11） 2015/04/23(木) 15:13

別ブックの場合、With Sheets("Sheet2") の部分を、With Workbooks("Book2.xlsm").Sheets("Sheet2") のように、ブック名付きに変えるだけですよ。

あと、些細な事なのですが、「ご存知の方…」というお願いの仕方は好ましくないです。プログラム系の回答の場合、
調べて、試して、そして回答しているのであって、知っているから即答している、というわけではないので。
（???） 2015/04/23(木) 15:21

ご指摘ありがとうございます。そのつもりはなかったのですが、不快にさせてしまい大変申し訳ありません。今後気を付けます。

厚かましいことを承知でもう一つお伺いしたいことがあるのですが、2つのコンボボックスで絞り込んだデータをリストボックス1に表示させたいのですが、コンボボックスをもとに、というのがうまくいきません。どのように改変すれば良いでしょうか。

データはデータ.xlsxのデータ表シートに入っています。出てきたデータをB列にある商品名をリストボックスに入れたいと思っています。

Workbooks.Open Filename : = "データ.xlsx"
Ecell = Workbooks("データ.xlsx").Worksheets(1).Range("B50").End(xlUp).Row
For i = 1 To Ecell
　With ListBox1
　　.AddItem
　　.List(i - 1) = Workbooks("データ.xlsx").Worksheets(1).Cells(i, 2)
　End With
Next i
（yuuki11） 2015/04/23(木) 16:10

データ.xlsxは、予め開いておく前提です。
（マクロで開く場合、UserForm_Initialize時にでもopenを追加すれば良いでしょう）

シートの1番目は、ComboBox2の内容を作成する際に参照したシートと同一ですかね？

 Private Sub ComboBox2_Change()
    Dim i As Long

    ListBox1.Clear

    With Workbooks("データ.xlsx").Sheets(1)
        For i = 1 To .Cells(.Rows.Count, "J").End(xlUp).Row
            If ComboBox1.Text = .Cells(i, "J").Value And _
               ComboBox2.Text = .Cells(i, "K").Value Then
                ListBox1.AddItem .Cells(i, "B").Value
            End If
        Next i
    End With
 End Sub
（???） 2015/04/23(木) 18:44

 横から失礼します。

 仮に都道府県や市と呼んでおられる項目とB列の商品と呼んでおられる項目との関係なんですが
 ・同じシートですか（???さんからも同様のコメントがありましたけど）
 ・で、それをリストボックスに表示するということは、J列の市と呼んでおられる項目は、ユニークではなく
 　同じものが複数存在するということですね？
 　で、ComboBox2には、それらをユニークにして表示するんですね？

 　ちなみに、データ.xlsx が保存されているフォルダはどこですか？
 　マクロブックと同じフォルダですか？

（β） 2015/04/24(金) 07:09

 別案です。
 データ.xlsxはマクロブックと同じフォルダに保存されているという前提。
 本来なら、Workbook_Openあたりで開いておくのがいいと思いますが、ここではユーザーフォームのInitializeで開いています。
 ですから、ユーザーフォームの表示の際、ちょっと時間がかかる可能性もあります。

 8:12 不要コードを１行削除（実害はありませんが）
 8:28 実害はめったにないけど、不具合で鵜ｒ可能性があるところを変更。

 Option Explicit

 Dim Dic As Object
 Dim stp As Boolean

 Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    Dim c As Range
    Dim pre As String
    Dim cty As String
    Dim com As String
    Application.ScreenUpdating = False
    Set Dic = CreateObject("Scripting.Dictionary")
    Set sh = Workbooks.Open(ThisWorkbook.Path & "\データ.xlsx").Sheets(1)

    For Each c In sh.Range("J1", sh.Range("J" & Rows.Count).End(xlUp))
        pre = c.Text
        cty = c.EntireRow.Range("K1").Text
        com = c.EntireRow.Range("B1").Text
        If Not Dic.exists(pre) Then
            Set Dic(pre) = CreateObject("Scripting.Dictionary")
        End If
        If Not Dic(pre).exists(cty) Then
            Set Dic(pre)(cty) = CreateObject("Scripting.Dictionary")
        End If
        Dic(pre)(cty)(com) = True
    Next

    sh.Parent.Close False
    ComboBox1.List = Dic.keys

 End Sub

 Private Sub ComboBox1_Change()
    ListBox1.Clear
    stp = True
    ComboBox2.Clear
    If ComboBox1.ListIndex > -1 Then ComboBox2.List = Dic(ComboBox1.Value).keys
    stp = False
 End Sub

 Private Sub ComboBox2_Change()
    If stp Then Exit Sub
    ListBox1.Clear
    If ComboBox2.ListIndex < 0 Then Exit Sub
    ListBox1.List = Dic(ComboBox1.Value)(ComboBox2.Value).keys
 End Sub

（β） 2015/04/24(金) 07:22

???さん、βさん、回答ありがとうございます。返信が遅くなり申し訳ありません。

データブックのシート1から全て参照しています。J列K列B列というのは全て同じシートです。
データブックが保存されているのはこのマクロ表と同じフォルダに格納されています。

お二方共、とても丁寧でわかりやすく助かりました。三日ほど悩んでいたのですが漸く一歩進むことができました！ありがとうございました。

（yuuki11） 2015/04/24(金) 09:55

コメント返信：

[ 一覧(最新更新順) ]

[[20150423143424]] 『コンボボックスの連携』（yuuki11） ページの最後に飛ぶ

[[20150423143424]]　『コンボボックスの連携』（yuuki11）　ページの最後に飛ぶ