マスタから候補生成（候補リストの自動作成）
Excel VBAで「マスタデータから候補を生成して入力補助に使いたい」という場面はよくあります。
例えば「商品マスタから商品名を選択」「顧客マスタから顧客名を選択」といったケースです。
初心者向けに、コード例やテンプレートをかみ砕いて説明します。
基本の考え方
- マスタとは？
候補の元になる一覧データ。商品一覧、顧客一覧、都市一覧など。 - 候補生成の流れ:
- シートにマスタデータを用意（例：商品一覧）。
- VBAでマスタを読み込む。
- ComboBoxやListBoxに候補を追加。
- 入力補助や選択肢として利用。
テンプレ1：マスタからComboBoxに候補生成
UserFormに ComboBox1 を配置。シート「Master」のA列に商品一覧があると仮定。
Private Sub UserForm_Initialize()
Dim ws As Worksheet: Set ws = Worksheets("Master")
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long
ComboBox1.Clear
For i = 2 To lastRow
ComboBox1.AddItem ws.Cells(i, 1).Value
Next i
End Sub
- ポイント:
- マスタシートのA列を候補に。
- フォームを開いたときに自動で候補が生成される。
テンプレ2：マスタからListBoxに候補生成
UserFormに ListBox1 を配置。
Private Sub UserForm_Initialize()
Dim ws As Worksheet: Set ws = Worksheets("Master")
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long
ListBox1.Clear
For i = 2 To lastRow
ListBox1.AddItem ws.Cells(i, 1).Value
Next i
End Sub
- ポイント:
- ListBoxに候補を一覧表示。
- 複数選択も可能。
テンプレ3：マスタから候補を動的に絞り込み（AutoComplete）
UserFormに TextBox1 と ListBox1 を配置。
Private Sub TextBox1_Change()
Dim ws As Worksheet: Set ws = Worksheets("Master")
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim inputText As String
inputText = TextBox1.Text
ListBox1.Clear
Dim i As Long
For i = 2 To lastRow
If inputText <> "" And LCase(ws.Cells(i, 1).Value) Like LCase(inputText & "*") Then
ListBox1.AddItem ws.Cells(i, 1).Value
End If
Next i
End Sub
Private Sub ListBox1_Click()
TextBox1.Text = ListBox1.Value
End Sub
- ポイント:
- 入力文字に一致する候補だけを表示。
- 候補をクリックするとTextBoxに反映。
テンプレ4：重複を排除して候補生成（Dictionary利用）
Private Sub UserForm_Initialize()
Dim ws As Worksheet: Set ws = Worksheets("Master")
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 2 To lastRow
If Not dict.exists(ws.Cells(i, 1).Value) Then
dict.Add ws.Cells(i, 1).Value, 1
End If
Next i
ComboBox1.Clear
ComboBox1.List = dict.Keys
End Sub
- ポイント:
- Dictionaryを使うと重複を排除できる。
- 候補リストがすっきりする。
例題で練習
'例1：商品マスタからComboBoxに候補生成
Sub Example1()
' → UserForm_Initialize にコードを記述
End Sub
'例2：顧客マスタからListBoxに候補生成
Sub Example2()
' → UserForm_Initialize にコードを記述
End Sub
'例3：入力途中で候補を絞り込み
Sub Example3()
' → TextBox1_Change にコードを記述
End Sub
'例4：重複を排除した候補生成
Sub Example4()
' → UserForm_Initialize にコードを記述
End Sub
初心者向けポイント
- マスタシートを用意するのが基本 → 候補の元データをExcelにまとめておく。
- Initializeイベントで候補生成 → フォームを開いたときに候補を読み込む。
- TextBoxのChangeイベントで絞り込み → 入力補助に便利。
- Dictionaryで重複排除 → 実務でよく使う。
👉 この「マスタから候補生成テンプレ」を覚えておけば、Excel VBAで マスタデータを元にした入力補助機能 を簡単に作成できます。