Excelで入力フォームを作るとき、「都道府県を選んだら、その都道府県の市区町村だけがプルダウンに表示される」という連動プルダウン（依存プルダウン）を使いたい場面は多いと思います。手作業で名前定義や入力規則を1つずつ設定するのは手間がかかりますが、VBAを使えば元データさえ用意しておけば一瞬で自動構築できます。
この記事では、名前付き範囲（Names）と入力規則（Validation）をVBAで自動生成し、都道府県を選択すると市区町村の選択肢が自動で絞り込まれる仕組みを作る方法を解説します。あわせて、親のプルダウンを変更したときに子のプルダウンの値をリセットするWorksheet_Changeイベントの使い方も紹介します。
連動プルダウンの仕組み
連動プルダウンは、次の2つの機能を組み合わせて実現します。
- 名前付き範囲（Name）：都道府県ごとに市区町村のリストへ名前を付けておく
- 入力規則（Validation）＋INDIRECT関数：選択された都道府県名を使って、対応する名前付き範囲をリストとして参照する
つまり「東京都」というセルの値をそのまま名前として使い、INDIRECT("東京都")のように参照することで、選んだ都道府県に応じたリストが自動的に切り替わる仕組みです。この名前定義の部分をVBAで一括作成します。
元データの準備
まず、別シート（ここでは「マスタ」シート）に以下のようなデータを用意します。1行目に都道府県名、2行目以降にその都道府県の市区町村を入力します。
| A列（東京都） | B列（大阪府） | C列（北海道） |
|---|---|---|
| 千代田区 | 大阪市 | 札幌市 |
| 新宿区 | 堺市 | 函館市 |
| 渋谷区 | 東大阪市 | 旭川市 |
このように都道府県ごとに列を分けてリストを作っておくのがポイントです。列数や行数は都道府県ごとに違っていても問題ありません。
VBAコード：名前付き範囲と入力規則を自動作成する
以下のマクロを実行すると、マスタシートの見出し行（都道府県名）ごとに名前付き範囲を自動作成し、入力シート（ここでは「入力」シート）のA列に都道府県のプルダウン、B列に市区町村の連動プルダウンを設定します。
Sub CreateDependentDropdown()
Dim wsMaster As Worksheet
Dim wsInput As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim c As Long
Dim prefName As String
Dim rngList As Range
Dim prefList As String
Set wsMaster = ThisWorkbook.Worksheets("マスタ")
Set wsInput = ThisWorkbook.Worksheets("入力")
lastCol = wsMaster.Cells(1, wsMaster.Columns.Count).End(xlToLeft).Column
prefList = ""
For c = 1 To lastCol
prefName = wsMaster.Cells(1, c).Value
If prefName <> "" Then
lastRow = wsMaster.Cells(wsMaster.Rows.Count, c).End(xlUp).Row
If lastRow >= 2 Then
Set rngList = wsMaster.Range(wsMaster.Cells(2, c), wsMaster.Cells(lastRow, c))
'同名の名前定義があれば削除してから登録し直す
On Error Resume Next
ThisWorkbook.Names(prefName).Delete
On Error GoTo 0
ThisWorkbook.Names.Add Name:=prefName, RefersTo:=rngList
End If
prefList = prefList & prefName & ","
End If
Next c
If Len(prefList) > 0 Then
prefList = Left(prefList, Len(prefList) - 1)
End If
'入力シートのA列（都道府県）に固定リストの入力規則を設定
With wsInput.Range("A2:A1000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=prefList
End With
'B列（市区町村）にINDIRECT関数で連動する入力規則を設定
With wsInput.Range("B2:B1000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=INDIRECT($A2)"
End With
MsgBox "連動プルダウンの設定が完了しました。", vbInformation
End Sub
コードのポイントは次のとおりです。
ThisWorkbook.Names.Addで、都道府県名をそのまま名前付き範囲の名前として登録しています。名前に使えない文字（スペースや記号など）が都道府県名に含まれることはないため、そのまま使えます。- 再実行してもエラーにならないよう、
On Error Resume Nextで同名の名前定義を一度削除してから登録し直しています。 - B列の入力規則には
Formula1:="=INDIRECT($A2)"を指定し、同じ行のA列の値（都道府県名）を名前として参照するようにしています。
都道府県を変更したら市区町村をリセットする
都道府県を選び直したときに、以前選んでいた市区町村がそのまま残っていると「東京都なのに大阪市が入っている」といった不整合が起こります。これを防ぐために、Worksheet_Changeイベントで対応する市区町村セルを自動クリアします。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
'A列（都道府県）が変更された場合のみ処理する
If Intersect(Target, Me.Range("A2:A1000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In Intersect(Target, Me.Range("A2:A1000"))
Me.Cells(cell.Row, "B").ClearContents
Next cell
Application.EnableEvents = True
End Sub
このコードは「入力」シートのシートモジュールに貼り付けます。Application.EnableEvents = False を挟んでいるのは、B列をクリアする操作自体がWorksheet_Changeを再度呼び出してしまう無限ループを防ぐためです。
実行時に注意したいポイント
- 都道府県名や市区町村名にスペースが含まれていると、名前付き範囲の登録時にエラーになります。マスタデータの見出しやリスト内容に余分な空白がないか事前に確認しましょう。
- マスタシートのリストを後から追加・変更した場合は、
CreateDependentDropdownを再実行すれば名前付き範囲と入力規則が最新の内容に更新されます。 - 入力規則の対象範囲（この記事では2〜1000行目）は、実際に使うデータ量に合わせて調整してください。範囲を広げすぎると動作が重くなる場合があります。
まとめ
この記事では、名前付き範囲とINDIRECT関数を組み合わせた連動プルダウンを、VBAで自動構築する方法を紹介しました。都道府県と市区町村のような親子関係のあるリストは、マスタデータさえ整えておけばマクロ1つで入力規則まで一括設定できます。
Worksheet_Changeイベントによる子セルのリセット処理もあわせて実装しておくと、入力ミスの少ない実用的なフォームに仕上がります。アンケートフォームや申請書など、選択項目が多いシートを作る際にぜひ活用してみてください。
コメント