値候補の動的生成（AutoComplete候補リストの作成）
Excel VBAで「入力途中の文字から候補を自動生成したい」「シートのデータを元に候補リストを作りたい」といった場面では 値候補の動的生成 が役立ちます。
初心者向けに、コード例やテンプレートをかみ砕いて説明します。
基本の考え方
- 候補リストを動的に作る方法
- シートのデータを読み込んで候補にする
- 配列やコレクションを使って候補を保持する
- TextBoxやComboBoxの入力イベントで候補を絞り込む
- メリット:
- 入力補助でミスを減らせる
- データベース的に使える
- 候補が増減しても自動対応できる
テンプレ1：シートのデータから候補を生成（ComboBox）
UserFormに ComboBox1 を配置。
Private Sub UserForm_Initialize()
Dim ws As Worksheet: Set ws = Worksheets("Data")
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
- ポイント:
- シート「Data」のA列を候補に。
- データが増えても自動で候補が更新される。
テンプレ2：入力途中で候補を絞り込み（ListBox）
UserFormに TextBox1 と ListBox1 を配置。
Private Sub TextBox1_Change()
Dim ws As Worksheet: Set ws = Worksheets("Data")
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
- ポイント:
- 入力文字に一致する候補だけをListBoxに表示。
- 候補をクリックするとTextBoxに反映。
テンプレ3：候補を配列から動的生成
Private Sub TextBox1_Change()
Dim candidates As Variant
candidates = Array("東京", "大阪", "名古屋", "札幌", "福岡")
Dim inputText As String
inputText = TextBox1.Text
ListBox1.Clear
Dim i As Long
For i = LBound(candidates) To UBound(candidates)
If inputText <> "" And LCase(candidates(i)) Like LCase(inputText & "*") Then
ListBox1.AddItem candidates(i)
End If
Next i
End Sub
- ポイント:
- 配列を候補リストに。
- 小規模な候補なら配列で十分。
テンプレ4：候補を重複なく生成（Dictionary利用）
Private Sub UserForm_Initialize()
Dim ws As Worksheet: Set ws = Worksheets("Data")
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：シートのデータを候補にする
Sub Example1()
' → UserForm_Initialize にコードを記述
End Sub
'例2：入力途中で候補を絞り込み
Sub Example2()
' → TextBox1_Change にコードを記述
End Sub
'例3：配列から候補を生成
Sub Example3()
' → TextBox1_Change にコードを記述
End Sub
'例4：重複を排除した候補生成
Sub Example4()
' → UserForm_Initialize にコードを記述
End Sub
初心者向けポイント
- 候補は「シート」「配列」「Dictionary」から作れる → データ量に応じて選択。
- TextBoxのChangeイベントで動的補完 → 入力が変わるたびに候補を更新。
- ListBoxやComboBoxに表示 → ユーザーが選びやすい。
- 重複排除はDictionaryが便利 → 実務でよく使う。
👉 この「値候補の動的生成テンプレ」を覚えておけば、Excel VBAで 入力補助機能を作り、ユーザーが効率的に正しいデータを入力できるフォーム を簡単に実装できます。