【ExcelVBA】郵便番号・振込先金融機関コードの入力補助フォーム例

ExcelVBAのユーザーフォームを用いて、
「できるだけ簡潔なコードによる入力補助フォーム例の作成」に挑戦しました。

リンク：【GitHub】郵便番号・振込先金融機関コードの入力補助フォーム例

実装した機能について
・ドロップダウンリストによる住所入力サジェスト＆郵便番号自動検索代入
・本店名-支店名入力による金融機関コード自動検索代入

本記事では、サンプルファイルの試用方法と実装コードの概要解説に分けてご紹介します。
ExcelVBAで同様の機能を実装しようとしている方の参考になれば幸いです。

本記事では説明を割愛している主な技能
・シートから配列へのデータ格納
・ブックに設定した「名前の定義」によるRangeオブジェクトの引数指定

サンプルファイルの試用方法（GitHub添付のReadMeより）

注意：サンプルファイル中の郵便番号と金融機関コードのリストは、ある時点でこちらのサイト等から独自に入手したものです。「内容に誤りが含まれる可能性がある」「最新情報ではない」等のリスクがあることにご留意ください。

操作方法

入力フォーム画面

郵便番号・住所入力補助

「郵便番号・住所入力補助」ボタン押下（または「住所上段」セルの選択）により入力補助フォームが表示されます。

自治体名-大字名の一部を入力すると、中間一致検索フィルタ処理により、合致するデータが格納されたリストボックスが表示されます。（サンプル対応範囲：千葉県内）

リストから対象を選択し、「決定」ボタン押下で郵便番号・住所を入力フォームに代入します。

振込先入力補助

「振込先入力補助」ボタン押下（または「本店名」セルの選択）により、同様のユーザーフォームが表示されます。
※郵便番号入力補助と同様の操作感となります。

1.「本店名」の一部を入力　→　本店名リストを展開
2.本店名リストから選択　　→　支店名リストを展開
3.「支店名」の一部を入力　→　本店コードと合致する支店名から絞り込み
※支店の特定には本店情報を必要とするため、先に本店名を特定（リスト選択）する必要が必要があります。

実装コードの概要解説

郵便番号・住所入力補助について（ユーザーフォームによる制御）

※「振込先入力補助」は、これとほぼ同様の機能が2つついたものとなるので割愛します。
※当初はComboBoxでしたが、どうにも.DropDownでリストが全開にならないのでListBoxに変更しました。

「入力フォーム」シート記載のイベントコード概要

「郵便番号・住所入力補助」ボタンが押下された
→UserForm1を表示する

「住所上段（定義名）」が選択された
→それが空白ならばUserForm1を表示する
※セル結合されているので.MergeAreaで結合セル全体を指定

「入力フォーム」シート

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Sheets("入力フォーム")
        Select Case Target.Address
            Case .Range("住所上段").MergeArea.Address
                If .Range("住所上段") = "" Then UserForm1.Show
                '「振込先入力補助」関係個所を割愛
        End Select
    End With
End Sub

UserForm1中の配置オブジェクト名（赤字）

UserForm1展開時の初期設定コード概要
※要点のみ抜粋
・「入力フォーム」シートの「郵便番号（定義名）」セルの値をクリア
　→予期せぬ操作により誤った値が反映されることを防ぐため
・TextBox1を選択（.SetFocus）
　→ユーザーがスムーズに入力できるよう配慮（複数入力枠がある場合に有効）
・ListBox1に格納する値を2列表示に設定（.ColumnCount = 2）
　→「郵便番号-住所」データを格納
・Textプロパティとなる列位置を2に設定（.TextColumn = 2）
　→「郵便番号-住所」データの2列目である「住所」がListBox1の値となる
　参考リンク：Microsoft「TextColumn プロパティ」
・ListBox1を非表示にする（.Visible = False）
　→ユーザーが入力枠（TextBox1）を見失わないよう配慮（周辺も高さ調整）

UserForm1

'フォーム初期化設定
Private Sub UserForm_Initialize()
    With Sheets("入力フォーム")
        .Range("郵便番号").ClearContents
    End With
    With Me
        .Height = 80
    End With
    With CommandButton1
        .Height = 20.25
    End With
    With TextBox1
        .SetFocus
        .IMEMode = fmIMEModeHiragana
        .ControlSource = "入力フォーム!住所上段"
    End With
    With ListBox1
        .ColumnCount = 2
        .TextColumn = 2
        .ColumnWidths = "70 pt;"
        .Height = 103
        .Visible = False
    End With
End Sub

UserForm1記載のイベントコード概要（フォーム上での操作に反応し実行）

TextBox1でキー操作（検索値入力）された
→「郵便番号一覧」シート2列目（住所）の文字列中、TextBox1に入力された検索値に合致（中間一致）する行のデータ（郵便番号-住所）のみListBox1に.Additem（リスト追加）し.Visible = True（リスト展開）する。
リスト追加操作の参考リンク：Office Tanaka「複数列のリストボックス」

ListBox1のリストの一部が選択された
→選択箇所のTextプロパティにあるデータ（.TextColumn = 2につき「住所」が該当）をTextBox1に代入する。

CommandButton1が押下された
→ListBox1の.TextColumnを1に変更（Textプロパティ：「住所」→「郵便番号」）し、「入力フォーム」シートの「郵便番号（定義名）」セルにListBox1、「住所上段（定義名）」セルにTextBox1の値をそれぞれ代入してUserForm1を閉じる。
※一部の特殊な（カッコ書きされている）住所データの加工処理を含む

UserForm1

'リストボックスを作成・表示
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim 終行 As Long, 行 As Long, 添字 As Long
    Dim 配列()
    With Sheets("郵便番号一覧")
        終行 = .Cells(Rows.Count, 1).End(xlUp).Row
        配列 = .Cells(2, 1).Resize(終行 - 1, 2).Value
    End With
    With ListBox1
        Select Case KeyCode
            '28:変換キー、29:無変換キー（定数なし）
            Case 28, 29, vbKeyBack, vbKeySpace, vbKeyDelete, vbKeyA To vbKeyZ, vbKey0 To vbKey9, vbKeyNumpad0 To vbKeyNumpad9
                .Clear
                For 行 = 1 To 終行 - 1
                    If 配列(行, 2) Like "*" & TextBox1.Text & "*" Then
                        添字 = 添字 + 1
                        .AddItem ""
                        .List(添字 - 1, 0) = 配列(行, 1)
                        .List(添字 - 1, 1) = 配列(行, 2)
                    End If
                Next
                .Visible = True
                Me.Height = 180
                CommandButton1.Height = 120
        End Select
    End With
End Sub

'リストボックス選択→テキストボックス1に代入
Private Sub ListBox1_Click()
    TextBox1.Text = ListBox1.Text
End Sub

'入力フォームへ代入
Private Sub CommandButton1_Click()
    With Sheets("入力フォーム")
        ListBox1.TextColumn = 1
        .Range("郵便番号") = ListBox1.Text
        Select Case InStr(TextBox1.Text, "（")
            Case Is > 0: .Range("住所上段") = Left(TextBox1.Text, InStr(TextBox1.Text, "（") - 1)
            Case Else: .Range("住所上段") = TextBox1.Text
        End Select
        Unload Me
    End With
End Sub

記事作成にあたっての感想

いかがでしたでしょうか。実はユーザーフォームを使うのは初だったので、至らぬ点もあるかもしれません…。
ユーザーフォームはプロパティが多く、難しいイメージでしたが、想定していたより簡単に形になったので、今後もどんどん作りたいと思います。
ユーザーフォームに初挑戦する際に「こんな記事があれば助かったのに」と、自身が思えるようなものとなる様、何か思い付き次第加筆修正したいと思います。