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


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

リンク：[【GitHub】郵便番号・振込先金融機関コードの入力補助フォーム例](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code)

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

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

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


## サンプルファイルの試用方法（GitHub添付のReadMeより）
:::note warn
**注意：サンプルファイル中の郵便番号と金融機関コードのリストは、ある時点で[こちらのサイト](https://www.post.japanpost.jp/zipcode/download.html)等から独自に入手したものです。「内容に誤りが含まれる可能性がある」「最新情報ではない」等のリスクがあることにご留意ください。**
:::
## 操作方法
#### 入力フォーム画面  
![入力フォーム画面](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code/assets/147408435/2badc5e3-df93-4c25-9551-16c8456a3d74)  
#### 郵便番号・住所入力補助
「郵便番号・住所入力補助」ボタン押下（または「住所上段」セルの選択）により入力補助フォームが表示されます。  
![UserForm1](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code/assets/147408435/3ed9aeda-8f9b-441d-aa6b-badaf272a39c)
自治体名-大字名の一部を入力すると、中間一致検索フィルタ処理により、合致するデータが格納されたリストボックスが表示されます。（サンプル対応範囲：千葉県内）  
![リストボックス展開](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code/assets/147408435/53330b7c-b924-4491-8762-7bb86cbfafe7)  
リストから対象を選択し、「決定」ボタン押下で郵便番号・住所を入力フォームに代入します。  
#### 振込先入力補助
「振込先入力補助」ボタン押下（または「本店名」セルの選択）により、同様のユーザーフォームが表示されます。  
※郵便番号入力補助と同様の操作感となります。
![UserForm2](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code/assets/147408435/ac65fbcd-6b4d-4393-9944-253bf7409a20)  
1.「本店名」の一部を入力　→　本店名リストを展開  
2.本店名リストから選択　　→　支店名リストを展開  
3.「支店名」の一部を入力　→　本店コードと合致する支店名から絞り込み  
※支店の特定には本店情報を必要とするため、先に本店名を特定（リスト選択）する必要が必要があります。
## 実装コードの概要解説
### 郵便番号・住所入力補助について（ユーザーフォームによる制御）
※「振込先入力補助」は、これとほぼ同様の機能が2つついたものとなるので割愛します。
※当初は`ComboBox`でしたが、どうにも`.DropDown`でリストが全開にならないので`ListBox`に変更しました。

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

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

**「住所上段（定義名）」が選択された**
→それが空白ならば`UserForm1`を表示する
※セル結合されているので`.MergeArea`で結合セル全体を指定
:::
```vb:「入力フォーム」シート
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`中の配置オブジェクト名（赤字）**
![オブジェクト配置](https://github.com/takayuki818/Supports-input-of-postal-code-and-transfer-code/assets/147408435/03baa3e9-2601-4398-90be-896bc50402fb)  

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

```vb: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
```
:::note info
**`UserForm1`記載のイベントコード概要（フォーム上での操作に反応し実行）**

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

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

**`CommandButton1`が押下された**
→`ListBox1`の`.TextColumn`を1に変更（Textプロパティ：「住所」→「郵便番号」）し、「入力フォーム」シートの「郵便番号（定義名）」セルに`ListBox1`、「住所上段（定義名）」セルに`TextBox1`の値をそれぞれ代入して`UserForm1`を閉じる。
※一部の特殊な（カッコ書きされている）住所データの加工処理を含む
:::
```vb: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
```
## 記事作成にあたっての感想
いかがでしたでしょうか。実はユーザーフォームを使うのは初だったので、至らぬ点もあるかもしれません…。
ユーザーフォームはプロパティが多く、難しいイメージでしたが、想定していたより簡単に形になったので、今後もどんどん作りたいと思います。
ユーザーフォームに初挑戦する際に「こんな記事があれば助かったのに」と、自身が思えるようなものとなる様、何か思い付き次第加筆修正したいと思います。