【VBA】入力規則のリストを別のセルの値に連動させる方法（コピペOK）

記事ID: 116
タイトル: 入力規則のリストを別のセルの値に連動させる方法
カテゴリ: シート操作
一次キーワード: VBA セル 入力規則 動的 連動
想定読者: 入力規則のドロップダウンを手作業で書き換えている実務担当者（VBA初心者〜初級者）
検索意図: セルの値に応じて入力規則のリストを自動で切り替えたい
読者の悩み（1文）: A列で「部門」を選んだら、B列のドロップダウンに「その部門の担当者」だけ表示したいが、手作業で都度書き換えるのが面倒すぎる。
読了後にできること（1文）: Worksheet_ChangeイベントとValidation.Addを組み合わせて、セルの値が変わるたびに隣のセルの入力規則リストを自動で切り替えられる。
前提条件:
  - Excel版: 2016以降 / Microsoft 365
  - OS: Windows 10/11
  - 保存形式: .xlsm（マクロ有効ブック）
  - 貼り付け場所: シートモジュール（対象シートのコードウィンドウ）
  - 実行方法: セルの値を変更すると自動実行（Worksheet_Changeイベント）
更新日: 2026-03-18

—

この記事でわかること

• セルの値（部門名など）に連動して、隣のセルのドロップダウンリストを自動で切り替えられる
• Worksheet_ChangeとValidation.Addの連携コードがコピペで動く
• EnableEventsの無限ループなど、連動リストで最もハマりやすい落とし穴を回避できる

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

どんな場面で使う？

• A列の部門選択に連動してB列の担当者リストを自動切替したいとき
• 入力規則のドロップダウンを手作業で書き換える手間をなくしたいとき
• マスタシートを更新するだけで全シートのリストが反映される仕組みを作りたいとき
• 3段階以上の連動（部門→課→担当者）を実装したいとき

—

完成イメージ（Before / After）

Before（手作業で入力規則を書き換え）:

1. A2に「営業部」と入力
2. B2の入力規則を開いて手動で「田中,山田,佐藤」に書き換え
3. A3に「製造部」と入力
4. B3の入力規則を開いて手動で「鈴木,高橋,伊藤」に書き換え
5. 部門が増えるたびに全セルの入力規則を手動修正…

After（VBAで自動連動）:

1. A2に「営業部」を選択 → B2のドロップダウンが自動で「田中,山田,佐藤」に切り替わる
2. A2を「製造部」に変更 → B2のドロップダウンが自動で「鈴木,高橋,伊藤」に切り替わる
3. マスタを更新するだけでリストが自動反映

—

Worksheet_ChangeイベントとValidation.Addを組み合わせれば、セルの値に連動するドロップダウンリスト（入力規則）をVBAで自動化できる。A列で「営業部」を選んだらB列のリストが「田中,山田,佐藤」に自動切り替え。コピペで動くコードを最小版と実務版の2段階で紹介する。

自分も以前、部門マスタが更新されるたびに入力規則を手作業で書き直していた。20シート分、1つずつドロップダウンの元データを修正する作業が正直めんどくさかった。1つ漏れるとユーザーから「リストに名前がないんですけど」と問い合わせが来て、地味にストレスだった。Worksheet_Changeで連動させてからは、マスタシートを更新するだけで対象シートのリストが自動で切り替わるようになった。問い合わせもゼロになった。同じように入力規則を手作業で管理して消耗している人が、この記事で自動連動をサクッと実装できるようになればうれしい。

なお、Worksheet_Changeイベントの基本は セルの値が変わったら自動実行する方法 を参照。入力規則（Validation.Add）の基本的な設定方法は 入力規則（ドロップダウンリスト）をVBAで一括設定する方法 で解説している。

—

実行前の準備

バックアップを取る

このコードは入力規則を上書きする。必ずファイルのコピーを別フォルダに保存してから実行する。

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

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

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

—

手順（コピペ → 実行まで約5分）

1. コードの貼り付け場所を開く（シートモジュール）

重要: このコードは「シートモジュール」に貼る。標準モジュールではない。

1. 対象のシートタブを右クリック
2. 「コードの表示」をクリック
3. VBE（コードを書く画面）が開き、そのシート専用のコードウィンドウが表示される

標準モジュール（「挿入」→「標準モジュール」で作るもの）に貼ると、Worksheet_Changeイベントが発火しないため動かない。必ずシートタブから開くこと。

2. コードを貼り付ける

コードウィンドウに、下のコードをそのままコピペする。

3. 動作確認

1. VBEを閉じてExcelシートに戻る
2. A列のセル（A2など）に部門名を入力する
3. B列のセルをクリックしてドロップダウンを開き、リストが切り替わっていることを確認する
4. VBEのイミディエイトウィンドウ（Ctrl+G）で ?Application.EnableEvents を実行し、True と表示されれば正常

—

コード（最小版）– セルの値に連動してドロップダウンを切り替え

A列に部門名を入力すると、B列のドロップダウンリストがその部門の担当者に自動で切り替わる。まずはこれで動きを確認する。

貼り付け場所: 対象シートのシートモジュール（シートタブを右クリック →「コードの表示」）

'============================================================
' ■ セルの値に連動してドロップダウンリストを切り替え（最小版）
'   → A列の部門名に応じて、B列の入力規則リストを自動変更
'   → 貼り付け場所: シートモジュール
'============================================================
Private Sub Worksheet_Change(ByVal Target As Range)

    '--- 複数セル同時変更は対象外
    If Target.Count > 1 Then Exit Sub

    '--- A列（1列目）の変更だけをトリガーにする
    If Target.Column <> 1 Then Exit Sub

    '--- 2行目以降のみ対象（1行目はヘッダー）
    If Target.Row < 2 Then Exit Sub

    '--- ★ 無限ループ防止（最重要）
    Application.EnableEvents = False

    On Error GoTo ErrHandler

    '--- 部門名に応じたリスト文字列を作成
    Dim listStr As String

    Select Case CStr(Target.Value)
        Case "営業部"
            listStr = "田中,山田,佐藤"
        Case "製造部"
            listStr = "鈴木,高橋,伊藤"
        Case "総務部"
            listStr = "中村,小林,加藤"
        Case ""
            '--- 空欄の場合は入力規則を削除して終了
            Target.Offset(0, 1).Validation.Delete
            GoTo ExitHandler          '← EnableEventsを復帰して終了
        Case Else
            listStr = ""
    End Select

    '--- B列（隣のセル）に入力規則を設定
    With Target.Offset(0, 1)
        .Validation.Delete                  '← 既存の入力規則を削除
        If listStr <> "" Then
            .Validation.Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Formula1:=listStr
            .Validation.InCellDropdown = True
        End If
        .Value = ""                          '← リスト変更時に既存値をクリア
    End With

ExitHandler:
    Application.EnableEvents = True          '← ★ 必ず復帰させる
    Exit Sub

ErrHandler:
    Application.EnableEvents = True          '← ★ エラー時も必ず復帰
    MsgBox "エラーが発生しました: " & Err.Description, vbExclamation
End Sub

書き換えポイント

ポイント: Application.EnableEvents = False を冒頭に入れ、処理の最後とエラー時の両方で True に戻す。これを忘れるとWorksheet_Changeが自分自身を呼び続けて無限ループになる。

—

コード（実務版）– マスタシート参照+エラー処理+複数列対応

実務では、リストの元データをコード内にベタ書きせず、別シート（マスタ）から取得する方が保守しやすい。この仕組みを覚えてからは、「部門→担当者→案件」のような3段階連動の入力フォームも組めるようになった。入力ミスが激減して、集計作業も格段に楽になった。

※ B列の入力規則と既存値が上書きされます。実行前にバックアップを取ってください。

マスタシートの構成

「マスタ」という名前のシートを作成し、以下のようにデータを入れる。

貼り付け場所: 対象シートのシートモジュール（シートタブを右クリック →「コードの表示」）

'============================================================
' ■ セルの値に連動してドロップダウンリストを切り替え（実務版）
'   → A列の部門名に応じて、「マスタ」シートから担当者を検索し
'     B列の入力規則リストを自動更新
'   → 貼り付け場所: シートモジュール
'============================================================
Private Sub Worksheet_Change(ByVal Target As Range)

    '--- ★書き換えポイント ---
    Const TRIGGER_COL As Long = 1       '← トリガー列（A列=1）
    Const LIST_COL As Long = 2          '← リスト設定先（B列=2）
    Const START_ROW As Long = 2         '← データ開始行（1行目はヘッダー）
    Const MASTER_SHEET As String = "マスタ"  '← マスタシート名
    Const MASTER_KEY_COL As Long = 1    '← マスタの部門名列（A列）
    Const MASTER_VAL_COL As Long = 2    '← マスタの担当者名列（B列）
    '--- ★ここまで ---

    '--- 複数セル同時変更は対象外
    If Target.Count > 1 Then Exit Sub

    '--- トリガー列以外は無視
    If Target.Column <> TRIGGER_COL Then Exit Sub

    '--- ヘッダー行は無視
    If Target.Row < START_ROW Then Exit Sub

    '--- ★ 無限ループ防止（最重要）
    Application.EnableEvents = False

    On Error GoTo ErrHandler

    Dim targetCell As Range
    Set targetCell = Me.Cells(Target.Row, LIST_COL)

    '--- 部門名が空なら入力規則を削除して終了
    Dim dept As String
    dept = Trim(CStr(Target.Value))

    If dept = "" Then
        targetCell.Validation.Delete
        targetCell.Value = ""
        GoTo ExitHandler
    End If

    '--- マスタシートの存在チェック
    Dim wsMaster As Worksheet
    On Error Resume Next                '← マスタシート取得のみResume Next
    Set wsMaster = ThisWorkbook.Worksheets(MASTER_SHEET)
    On Error GoTo ErrHandler

    If wsMaster Is Nothing Then
        MsgBox "「" & MASTER_SHEET & "」シートが見つかりません。" & vbCrLf & _
               "マスタシートを作成してください。", vbExclamation
        GoTo ExitHandler
    End If

    '--- マスタから該当部門の担当者を取得
    Dim lastRowMaster As Long
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, MASTER_KEY_COL).End(xlUp).Row

    Dim listStr As String
    listStr = ""

    Dim r As Long
    Dim masterDept As String
    Dim memberName As String

    For r = 2 To lastRowMaster
        masterDept = Trim(CStr(wsMaster.Cells(r, MASTER_KEY_COL).Value))
        If masterDept = dept Then
            memberName = Trim(CStr(wsMaster.Cells(r, MASTER_VAL_COL).Value))
            If memberName <> "" Then
                If listStr <> "" Then listStr = listStr & ","
                listStr = listStr & memberName
            End If
        End If
    Next r

    '--- リスト文字列が255文字を超える場合の対策
    If Len(listStr) > 255 Then
        MsgBox "担当者リストが255文字を超えています。" & vbCrLf & _
               "マスタのデータを見直してください。", vbExclamation
        GoTo ExitHandler
    End If

    '--- B列に入力規則を設定
    With targetCell
        .Validation.Delete                  '← 既存の入力規則を削除
        If listStr <> "" Then
            .Validation.Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Formula1:=listStr
            .Validation.InCellDropdown = True
        Else
            '--- マスタに該当部門がない場合
            .Value = ""
        End If
        .Value = ""                          '← 部門変更時に既存の担当者をクリア
    End With

ExitHandler:
    Application.EnableEvents = True          '← ★ 必ず復帰させる
    Exit Sub

ErrHandler:
    Application.EnableEvents = True          '← ★ エラー時も必ず復帰
    MsgBox "エラーが発生しました: " & Err.Description, vbExclamation

End Sub

書き換えポイント

Me はシートモジュール内ではそのシート自身を指す。標準モジュールでは使えないため、このコードは必ずシートモジュールに貼ること。

—

よくある落とし穴5選

1. EnableEvents = False を忘れて無限ループ → Excelフリーズ

自分もこれで痛い目に遭った。最初にWorksheet_Changeの中でセルの値をクリアするコードを書いたとき、EnableEvents = Falseを入れ忘れてExcelがフリーズした。タスクマネージャーで強制終了するハメになり、保存前のデータが飛んだ。もっと早く知りたかった。

原因: Worksheet_Change内でセルの値を変更すると、再びWorksheet_Changeが発火する。EnableEventsで一時停止しないと無限再帰に陥る。

対策: 処理の冒頭で Application.EnableEvents = False、最後とエラーハンドラの両方で True に戻す。

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False    '← 冒頭で停止
    On Error GoTo ErrHandler
    ' ... 処理 ...
ExitHandler:
    Application.EnableEvents = True     '← 必ず復帰
    Exit Sub
ErrHandler:
    Application.EnableEvents = True     '← エラー時も復帰
End Sub

2. コードを標準モジュールに貼って「動かない」

原因: Worksheet_Changeはシートモジュール専用のイベントプロシージャ。標準モジュール（「挿入」→「標準モジュール」で作るもの）に貼っても発火しない。

対策: 対象シートのタブを右クリック →「コードの表示」で開くシートモジュールに貼る。

3. Validation.Deleteを入れ忘れてエラー1004

原因: 既に入力規則が設定されているセルにValidation.Addを実行すると、「実行時エラー ‘1004’」が出る場合がある。

対策: Validation.Add の直前に必ず .Validation.Delete を入れる。

4. リスト文字列が255文字を超えてエラー

原因: Validation.AddのFormula1にカンマ区切り文字列を直接指定する場合、255文字の制限がある。担当者が多い部門ではこの上限に引っかかる。

対策: 実務版コードでは255文字チェックを入れている。超える場合は、マスタ範囲を名前定義で参照する方法に切り替える。名前定義の詳細は 名前定義（名前の管理）を一括作成・削除する方法 を参照。

5. Target.Countを考慮せず、複数セル一括変更でエラー

原因: セルを範囲選択してDeleteキーで一括削除すると、Targetが複数セルになる。Target.Value で単一の値を取得しようとするとエラーになる。

対策: 冒頭に If Target.Count > 1 Then Exit Sub を入れる。

—

VBAの連動リストでEnableEventsが戻らなくなったときの対処法

「Worksheet_Changeが動かなくなった」という場合、原因はEnableEventsがFalseのまま復帰していないことだ。VBEのイミディエイトウィンドウ（Ctrl+G）で Application.EnableEvents = True と入力してEnterを押せば復帰する。

VBAの入力規則で255文字制限エラーが出るときの対処法

「Validation.Addで実行時エラーが出る」という場合、原因はFormula1に渡すカンマ区切り文字列が255文字を超えていること。対策は名前定義で範囲参照に切り替えるか、マスタの項目数を見直す。

FAQ

Q1: 3段階の連動（部門→課→担当者）にできる？

できる。Worksheet_Changeの中で Target.Column を判定し、列ごとに処理を分ける。

Select Case Target.Column
    Case 1  '← A列（部門）が変わったらB列（課）のリストを更新
        ' B列の入力規則を更新する処理
    Case 2  '← B列（課）が変わったらC列（担当者）のリストを更新
        ' C列の入力規則を更新する処理
End Select

Q2: マスタデータを別ブック（別ファイル）から取得できる？

可能だが、そのブックが開いている必要がある。閉じたブックからデータを取得する場合は 複数ブックを開かずにデータ取得する方法 を参照。ただし、入力規則の連動は即時性が求められるため、同一ブック内のマスタシートに置くのが実用的。

Q3: INDIRECTと名前定義で連動させる方法との違いは？

項目が少なく固定的ならINDIRECT+名前定義が手軽。項目が多い・頻繁に変わる・3段階以上ならVBAの方が管理しやすい。

Q4: EnableEventsがFalseのまま戻らなくなったら？

イミディエイトウィンドウ（VBEで Ctrl+G）に以下を入力してEnterを押す。

Application.EnableEvents = True

これで復帰する。?Application.EnableEvents で現在の状態を確認できる。

Q5: シートを保護したまま連動リストを動かせる？

UserInterfaceOnly:=True オプション付きで保護すれば、VBAからの操作は許可される。

Me.Protect Password:="pass", UserInterfaceOnly:=True

ただし、この設定はブックを閉じるとリセットされる。ブックを開くたびに自動で再設定するには、Workbook_Openイベントを使う。詳細は 特定シートだけ保護・解除する方法 を参照。

—

まとめ

• Worksheet_Change + Validation.Add: セルの値に連動するドロップダウンリストを実現する組み合わせ
• EnableEvents = False / True: 無限ループ防止の最重要ポイント。冒頭で停止、最後とエラー時に復帰
• Validation.Delete → Validation.Add: 既存の入力規則を削除してから再設定する
• シートモジュールに貼る: 標準モジュールではWorksheet_Changeが動かない
• マスタシート方式: コード内にリストをベタ書きせず、別シートから取得する方が保守しやすい

関連記事

• セルの値が変わったら自動実行する方法 — Worksheet_Changeイベントの基本
• 入力規則（ドロップダウンリスト）をVBAで一括設定する方法 — Validation.Addの基本操作
• 名前定義（名前の管理）を一括作成・削除する方法 — 255文字制限を回避する名前定義の活用
• 文字列⇔数値の型変換で計算エラーを防ぐ方法 — CStr, CLngなど型変換の基本
• 画面更新・再計算を止めてマクロを高速化する方法 — EnableEvents以外の高速化テクニック

—

次にやりたくなること

• 入力フォーム（UserForm）で手入力ミスを防ぐ方法: ドロップダウンだけでは足りない場合、UserFormで入力画面を作ればミスを減らせる
• 特定シートだけ保護・解除する方法: 連動リストを設定したシートを保護して入力規則を壊されないようにしたい場合
• 名前定義（名前の管理）を一括作成・削除する方法: 255文字制限を回避するために名前定義でリストを管理したい場合