Windows 10 Pro_x64 + Excel2013_x86 VBA で ListBox 2個を連動させる

Excel VBA のUserForm上の2個のListBoxを連動する時のメモ

普段はデータベースからひらってきたデータを表示することが多いんだけど

最小限必要なコードを張りつけてみる

・UserFormを1個追加する

・UserForm2の上にListBoxを2個追加する

・UserForm_Initialize で ListBox1 に初期データを追加する

・ListBox1をダブルクリックしたときに、ListBox2に対応するデータを表示する

対象データは以下の全国一括(KEN_ALL.CSV)より一部抜粋

郵便番号データダウンロード

サンプルコード

Option Explicit

Private Sub UserForm_Initialize()
'
    ListBox1.Clear
    ListBox1.ColumnWidths = "30 pt;40"
    ListBox1.ColumnCount = 2
'
    ListBox1.AddItem ""
    ListBox1.List(0, 0) = "01"
    ListBox1.List(0, 1) = "北海道"
    ListBox1.AddItem ""
    ListBox1.List(1, 0) = "02"
    ListBox1.List(1, 1) = "青森県"
    ListBox1.AddItem ""
    ListBox1.List(2, 0) = "03"
    ListBox1.List(2, 1) = "岩手県"
'
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'
    Dim strCode     As String
    Dim strName     As String
'
    If ListBox1.ListIndex >= 0 Then
        strCode = ListBox1.List(ListBox1.ListIndex, 0)
        strName = ListBox1.List(ListBox1.ListIndex, 1)
'
        Call SetPostal(strCode)
'
        Label1.Caption = Trim(strName)
    End If
'
End Sub

Private Sub SetPostal(ByRef aPrefCode As String)
'
    ListBox2.Clear
    ListBox2.ColumnWidths = "30 pt;40 pt;70 pt;70 pt"
    ListBox2.ColumnCount = 4
'
    Select Case aPrefCode
    Case "01"
            ListBox2.AddItem ""
            ListBox2.List(0, 0) = "01101"
            ListBox2.List(0, 1) = "0640954"
            ListBox2.List(0, 2) = "札幌市中央区"
            ListBox2.List(0, 3) = "宮の森四条"
            ListBox2.AddItem ""
            ListBox2.List(1, 0) = "01102"
            ListBox2.List(1, 1) = "0028071"
            ListBox2.List(1, 2) = "札幌市北区"
            ListBox2.List(1, 3) = "あいの里一条"
    Case "02"
            ListBox2.AddItem ""
            ListBox2.List(0, 0) = "02201"
            ListBox2.List(0, 1) = "0301271"
            ListBox2.List(0, 2) = "青森市"
            ListBox2.List(0, 3) = "六枚橋"
            ListBox2.AddItem ""
            ListBox2.List(1, 0) = "02202"
            ListBox2.List(1, 1) = "0361516"
            ListBox2.List(1, 2) = "弘前市"
            ListBox2.List(1, 3) = "藍内"
     Case "03"
            ListBox2.AddItem ""
            ListBox2.List(0, 0) = "03201"
            ListBox2.List(0, 1) = "0200886"
            ListBox2.List(0, 2) = "盛岡市"
            ListBox2.List(0, 3) = "若園町"
            ListBox2.AddItem ""
            ListBox2.List(1, 0) = "03202"
            ListBox2.List(1, 1) = "0270202"
            ListBox2.List(1, 2) = "宮古市"
            ListBox2.List(1, 3) = "赤前"
    End Select
'
End Sub

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'
    Dim setVal1 As String
    Dim setVal2 As String
    Dim setVal3 As String
    Dim setVal4 As String
'
    If ListBox2.ListIndex >= 0 Then
        setVal1 = ListBox2.List(ListBox2.ListIndex, 0)
        setVal2 = ListBox2.List(ListBox2.ListIndex, 1)
        setVal3 = ListBox2.List(ListBox2.ListIndex, 2)
        setVal4 = ListBox2.List(ListBox2.ListIndex, 3)
    End If
'
    Debug.Print setVal1, setVal2, setVal3, setVal4
'
End Sub