【VBA】セル入力時にオートコンプリート（自動補完）を実装する方法（コピペOK）

この記事でわかること

• VBAでセルに文字を入力するだけで候補リストから自動補完できる
• Worksheet_Changeイベントで前方一致・部分一致の候補表示を実装できる
• マスタシートと連動して候補リストを動的に管理できる

対象: Excel 2016以降 / Microsoft 365、Windows 10/11

—

どんな場面で使う？

• 商品名や取引先名など、候補が多い項目をセルに入力する作業を効率化したいとき
• 入力規則のドロップダウンでは候補が多すぎて選びにくいとき
• マスタシートの候補リストと連動して、新しい候補が自動反映される仕組みを作りたいとき
• 入力ミス（タイプミス・表記ゆれ）を防いでデータの品質を上げたいとき

完成イメージ（Before / After）

Before（手入力で商品名や取引先名を入力）:

After（数文字入力で候補が自動表示）:

—

自分も以前、200件以上ある商品名を毎回手入力していた。入力規則のドロップダウンリストも試したが、候補が多すぎてスクロールがしんどかった。オートコンプリートをVBAで実装してからは、2〜3文字打つだけで候補が絞り込まれるようになった。入力ミスも激減して、正直もっと早く知りたかった。この記事で、同じように入力作業に時間を取られている人がラクになればうれしい。

入力規則のドロップダウンは候補が少ないうちは便利だが、100件を超えると探すのが大変。オートコンプリートなら数文字で候補を絞り込める。

なお、入力規則の基本的な設定方法は 入力規則（ドロップダウンリスト）をVBAで一括設定する方法 を参照。セルの値が変わったときの自動実行の仕組みは セルの値が変わったら自動で処理を実行する方法 で詳しく解説している。

—

実行前の準備

バックアップを取る

Worksheet_Changeイベントはセルの値を自動で書き換える。 既存データがあるシートで試す場合は、必ずファイルをコピーしてバックアップを取ること。

Excelをマクロ有効ブック（.xlsm）で保存する

拡張子が .xlsx のままだとマクロが保存できない。

1. 「ファイル」→「名前を付けて保存」
2. ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
3. 保存

シート構成を確認する

• 入力シート — A列に入力する（オートコンプリート対象列）
• 「マスタ」シート（実務版で使用） — A列に候補リスト（商品名、取引先名など）

基本版はコード内に候補リストを直接書くので、マスタシートは不要。

—

手順（コピペ → 動作確認まで約5分）

VBE（コードを書く画面）を開く

1. Excelで Alt + F11 を押す

シートモジュールを開く（標準モジュールではない）

ここが最大のポイント。 Worksheet_Changeはシートモジュールに書く。

1. VBEの左ツリーでオートコンプリートを設定したいシート名（例: Sheet1）をダブルクリック
2. 右側にコードウィンドウが開く → ここにコードを貼り付ける

間違いやすいポイント:

• 「挿入」→「標準モジュール」は使わない
• 左のツリーでシート名をダブルクリックする

コードを貼り付けて動作確認する

1. 下の「コード（基本版）」をコピーして貼り付ける
2. Excelの画面に戻る（Alt + F11）
3. A列に「り」と入力してEnterを押す → セルの値が「りんご」に変わればOK

—

コード（基本版）– 完全前方一致で候補セット

まずはこれだけで動く。A列にテキストを入力すると、候補リストから前方一致する最初の値を自動でセルにセットする。

★ 貼り付け先はシートモジュール（標準モジュールではない）

Private Sub Worksheet_Change(ByVal Target As Range)
    '--- セル入力時にオートコンプリート（基本版） ---
    Dim candidates As Variant
    Dim i As Long
    Dim inputVal As String

    ' A列以外は無視
    If Target.Column <> 1 Then Exit Sub
    ' 複数セル同時変更は無視
    If Target.Count > 1 Then Exit Sub
    ' 空欄は無視
    If Target.Value = "" Then Exit Sub

    ' 候補リスト（ここを書き換える）
    candidates = Array("りんご", "りんごジュース", "みかん", _
                       "バナナ", "ぶどう", "メロン", "もも")

    inputVal = CStr(Target.Value)

    ' 前方一致で候補を検索
    Application.EnableEvents = False
    For i = LBound(candidates) To UBound(candidates)
        If Left(candidates(i), Len(inputVal)) = inputVal Then
            Target.Value = candidates(i)
            Exit For
        End If
    Next i
    Application.EnableEvents = True
End Sub

書き換えポイント:

—

コード（実務版）– 部分一致＋コンボボックス表示＋マスタシート連動＋複数列対応

自分はこの実務版を使ってから、商品名や取引先名の入力が格段に速くなった。マスタシートに候補を追加するだけで自動的にオートコンプリートに反映されるのが便利。

シートモジュールに貼り付ける部分:

Private Sub Worksheet_Change(ByVal Target As Range)
    '--- セル入力時にオートコンプリート（実務版） ---
    Dim wsMaster As Worksheet
    Dim lastRowMaster As Long
    Dim inputVal As String
    Dim candidates() As String
    Dim candidateCount As Long
    Dim i As Long
    Dim masterCol As Long
    Dim cellVal As String

    ' ここが追加：複数列対応（A列=マスタA列、C列=マスタB列）
    Select Case Target.Column
        Case 1: masterCol = 1    ' A列入力 → マスタA列（商品名）
        Case 3: masterCol = 2    ' C列入力 → マスタB列（取引先名）
        Case Else: Exit Sub      ' それ以外は無視
    End Select

    ' 複数セル同時変更は無視
    If Target.Count > 1 Then Exit Sub
    ' 空欄は無視
    If Target.Value = "" Then Exit Sub

    inputVal = CStr(Target.Value)

    ' ここが追加：マスタシートから候補を取得
    On Error Resume Next
    Set wsMaster = ThisWorkbook.Sheets("マスタ")
    On Error GoTo 0
    If wsMaster Is Nothing Then Exit Sub

    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, masterCol).End(xlUp).Row
    If lastRowMaster < 2 Then Exit Sub

    ' ここが追加：部分一致で候補を収集
    candidateCount = 0
    ReDim candidates(1 To lastRowMaster)

    For i = 2 To lastRowMaster
        cellVal = CStr(wsMaster.Cells(i, masterCol).Value)
        If InStr(1, cellVal, inputVal, vbTextCompare) > 0 Then
            candidateCount = candidateCount + 1
            candidates(candidateCount) = cellVal
        End If
    Next i

    If candidateCount = 0 Then Exit Sub

    Application.EnableEvents = False

    ' ここが追加：候補が1件なら即セット、複数ならコンボボックス表示
    If candidateCount = 1 Then
        Target.Value = candidates(1)
    Else
        ' コンボボックスで候補を表示
        ShowCandidateCombo Target, candidates, candidateCount
    End If

    Application.EnableEvents = True
End Sub

Private Sub ShowCandidateCombo(ByVal Target As Range, _
                                ByRef candidates() As String, _
                                ByVal cnt As Long)
    '--- ここが追加：アクティブXコンボボックスで候補表示 ---
    Dim cb As OLEObject
    Dim cbName As String
    cbName = "cmbAutoComplete"

    ' 既存のコンボボックスがあれば削除
    On Error Resume Next
    Me.OLEObjects(cbName).Delete
    On Error GoTo 0

    ' コンボボックスを作成（セルの位置に合わせる）
    Set cb = Me.OLEObjects.Add( _
        ClassType:="Forms.ComboBox.1", _
        Left:=Target.Left, _
        Top:=Target.Top + Target.Height, _
        Width:=Target.Width + 50, _
        Height:=20)
    cb.Name = cbName

    ' 候補を追加
    Dim cmb As MSForms.ComboBox
    Set cmb = cb.Object
    cmb.Clear
    Dim j As Long
    For j = 1 To cnt
        cmb.AddItem candidates(j)
    Next j

    cmb.ListRows = WorksheetFunction.Min(cnt, 8)
    cb.Activate
    cmb.DropDown
End Sub

標準モジュールに貼り付ける部分（コンボボックス選択時の処理）:

Sub AutoComplete_ComboSelect()
    '--- コンボボックスの値をセルにセットして削除する ---
    Dim ws As Worksheet
    Dim cb As OLEObject
    Dim cmb As MSForms.ComboBox

    Set ws = ActiveSheet
    On Error Resume Next
    Set cb = ws.OLEObjects("cmbAutoComplete")
    On Error GoTo 0
    If cb Is Nothing Then Exit Sub

    Set cmb = cb.Object
    If cmb.Value <> "" Then
        Application.EnableEvents = False
        ActiveCell.Value = cmb.Value
        Application.EnableEvents = True
    End If

    cb.Delete
End Sub

自分はこの実務版を商品名入力に使っている。マスタシートに新商品を追加するだけで候補に反映されるので、メンテナンスがラクになった。

書き換えポイント:

入力規則との組み合わせは 入力規則のリストを別のセルの値に連動させる方法 を参照。

—

よくある落とし穴6選

VBAのオートコンプリートが動かないときの対処法

「コードを貼り付けたのにセル入力しても何も起きない」場合、コードを標準モジュールに貼り付けていることが原因だ。Worksheet_Changeイベントはシートモジュール専用なので、VBEの左ツリーで対象シート（例: Sheet1）をダブルクリックして、そこに貼り付けよう。

VBAのオートコンプリートでExcelがフリーズするときの対処法

「セルに入力した瞬間にExcelが固まる」場合、Application.EnableEvents = Falseを書き忘れていて無限ループが発生していることが原因だ。Worksheet_Change内でセルの値を変更すると自身が再発火する。コードの先頭でEnableEvents = False、末尾でTrueに戻す処理を必ず入れよう。

—

FAQ

Q1. Excel標準のオートコンプリート（入力候補）とどう違う？

Excel標準のオートコンプリートは同じ列の既存データから候補を出すだけで、カスタマイズできない。VBA版なら候補リストをマスタシートで管理でき、部分一致や複数列対応も可能。

Q2. 入力規則（ドロップダウンリスト）とどちらがよい？

候補が30件以下なら入力規則のドロップダウンで十分。50件以上ならオートコンプリートのほうが快適。入力規則の設定方法は 入力規則（ドロップダウンリスト）をVBAで一括設定する方法 を参照。

Q3. UserFormで候補一覧を表示する方法はある？

ある。コンボボックスの代わりにUserFormのリストボックスを使えば、候補のプレビューや複数列表示もできる。UserFormの作り方は ユーザーフォームで本格的な入力画面を作る方法 を参照。自分は商品名＋単価を並べて表示するのにUserForm版を使っている。

Q4. 数値入力でもオートコンプリートは使える？

使える。ただし、数値の場合は CStr(Target.Value) で文字列に変換してから比較する必要がある。商品コード（数字）で商品名を引きたい場合は、マスタシートのA列にコード、B列に商品名を入れ、Cells(i, 2).Value で商品名を取得する方法が実用的。

Q5. 保護されたシートでもオートコンプリートは動く？

シート保護が有効な場合、VBAからのセル書き込みがブロックされる。UserInterfaceOnly:=True で保護すれば、VBAからの操作は許可される。保護の詳細は 特定シートだけ保護・解除する方法 を参照。

—

—

まとめ

この記事では、VBAでセル入力時にオートコンプリート（自動補完）を実装する方法を解説した。

• 基本版 — Worksheet_Changeイベントで候補リストから前方一致の値を自動セット
• 実務版 — 部分一致検索＋コンボボックス候補表示＋マスタシート連動＋複数列対応

候補が多いデータの入力作業が格段に速くなり、入力ミスも減る。

関連記事:

• 入力規則（ドロップダウンリスト）をVBAで一括設定する方法 — 候補が少ない場合はドロップダウンリストのほうがシンプル。オートコンプリートとの使い分けが分かる
• セルの値が変わったら自動で処理を実行する方法 — Worksheet_Changeイベントの基本を詳しく知りたい場合に
• ユーザーフォームで本格的な入力画面を作る方法 — コンボボックスではなくUserFormで候補を表示したい場合に

—

次にやりたくなること

• ユーザーフォームで本格的な入力画面を作る方法 — オートコンプリートに加えて、入力フォーム全体をUserFormで構築したい場合に
• 入力規則のリストを別のセルの値に連動させる方法 — カテゴリ選択に応じて候補リストを切り替えたい場合に
• セルの入力値をVBAでチェック・制限する方法 — オートコンプリート後にさらに入力値のバリデーション（数値チェック・日付チェック等）を追加したい場合に
• セルの値が変わったら自動で処理を実行する方法 — オートコンプリート以外のWorksheet_Change活用パターンを知りたい場合に

—

Part 2: ルーブリック自己採点