Excelの「入力規則（データの入力規則）」でドロップダウンリストを作るとき、 1つずつ範囲を選んで → データの入力規則 → 設定… とやるのは結構面倒ですよね。
しかも、同じリストをたくさんのセルに設定したいとき、 毎回同じ操作を繰り返すのは時間のムダです。
そこで今回は、ExcelVBAで入力規則（ドロップダウン）を一括で設定するマクロをご紹介します。
- 別シートにマスタリストを作る
- マクロ実行で、選択範囲すべてに入力規則を設定
- リストを変更したいときも元のマスタを直すだけ
今回作る仕組みのイメージ
① マスタ用シート（シート名：マスタ）
リストに入れたい項目をA列に入力してください。そしてシート名を「マスタ」に変更してください。
② 入力用シート（シート名：入力）
新しいシートを挿入し、A列に項目を入力してください。
シート名を「入力」に変更してください。
マクロを実行すると、「入力」シートのB列（選択範囲）に、 「マスタ」シートのA列のリストを使ったドロップダウンが一括で設定されます。
入力規則を一括で作るマクロ（コピペOK）
まずは完成版のVBAコードから。
Option Explicit
' 選択した範囲に「マスタ」シートのリストを使った入力規則を一括設定するマクロ
Sub SetDataValidationFromMaster()
Dim wsMaster As Worksheet
Dim wsTarget As Worksheet
Dim lastRow As Long
Dim listRange As Range
Dim targetRange As Range
Dim formulaText As String
' マスタシートと入力シートを指定
Set wsMaster = ThisWorkbook.Worksheets("マスタ") ' リストのシート名
Set wsTarget = ThisWorkbook.Worksheets("入力") ' 入力側のシート名
' マスタの最終行を取得（A列にリストが並んでいる前提）
lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
Set listRange = wsMaster.Range("A1:A" & lastRow)
' 入力規則を設定したい範囲を取得（事前に選択しておく）
If TypeName(Selection) <> "Range" Then
MsgBox "入力規則を設定したいセル範囲を選択してから実行してください。", vbExclamation
Exit Sub
End If
Set targetRange = Selection
' 入力規則の元になる範囲（数式用の文字列）を作成
' 別シートのリストを参照するため R1C1 形式を使う方法もありますが、
' ここではアドレスを文字列として直接指定します。
formulaText = "=" & wsMaster.Name & "!" & listRange.Address
' 既存の入力規則を一旦クリア
On Error Resume Next
targetRange.Validation.Delete
On Error GoTo 0
' 入力規則（リスト）を一括設定
With targetRange.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=formulaText
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
MsgBox "入力規則（ドロップダウン）を設定しました。", vbInformation
End Sub
コードのポイント解説（初心者向け）
① マスタ側のリスト範囲を取得
lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
Set listRange = wsMaster.Range("A1:A" & lastRow)
「マスタ」シートの A 列に、リストの選択肢が上からズラッと並んでいる前提です。 最終行を自動で検出して範囲を決めているので、行数が増減してもそのまま使えます。
② 入力規則を設定したい範囲は「ユーザーが選択」
If TypeName(Selection) <> "Range" Then
MsgBox "入力規則を設定したいセル範囲を選択してから実行してください。", vbExclamation
Exit Sub
End If
Set targetRange = Selection
マクロを実行する前に、 「入力」シートで、入力規則を設定したいセル範囲を選択しておくイメージです。
③ 別シートのリストを入力規則の元にする書き方
formulaText = "=" & wsMaster.Name & "!" & listRange.Address
入力規則の「元の値」のところに、 「=マスタ!$A$1:$A$5」 のような形で文字列を渡してあげています。
④ 既存の入力規則は一旦削除してから設定
On Error Resume Next
targetRange.Validation.Delete
On Error GoTo 0
何度実行してもエラーにならないように、 先に古い入力規則を削除してから新しく設定しています。
実行手順
- 「マスタ」シートを作成し、A列に選択肢をリスト化する
- 「入力」シートに、入力したい列（例：B列「部署」）を用意する
- Alt + F11 でVBEを開き、「挿入」→「標準モジュール」で新しいモジュールを追加
- 先ほどの
SetDataValidationFromMasterマクロを貼り付ける - Excelに戻り、「入力」シートで B列の対象セル範囲を選択
- 「開発」タブ →「マクロ」→
SetDataValidationFromMasterを実行
これで、選択範囲のセルに一括でドロップダウンの入力規則が設定されます。
応用編：数値の制限を一括で設定するマクロ
リストだけでなく、 数値の範囲（例：1〜100だけ入力可）を一括で設定したい場面もよくあります。
その場合は、こんなマクロも使えます。
Sub SetNumericValidation()
Dim targetRange As Range
If TypeName(Selection) <> "Range" Then
MsgBox "入力規則を設定したいセル範囲を選択してから実行してください。", vbExclamation
Exit Sub
End If
Set targetRange = Selection
On Error Resume Next
targetRange.Validation.Delete
On Error GoTo 0
With targetRange.Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="1", _
Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = False
End With
MsgBox "1～100の範囲のみ入力可能な入力規則を設定しました。", vbInformation
End Sub
これを応用することで、 日付だけを許可したり、文字数の最大数を制限したり、 さまざまな入力ルールを一括設定できます。
まとめ：入力規則はVBAで一括設定してしまおう
今回のマクロのポイントをまとめると、次のとおりです。
- 別シートに「マスタリスト」を作っておく
- 入力側のセル範囲を選択してからマクロ実行
- 入力規則（リスト）を一括で設定できる
- 数値や日付など、他の入力規則にも応用可能
入力規則の設定は、一つひとつ手作業で行うと非常に時間がかかります。 VBAで一括設定できる仕組みを作っておけば、 新しいファイルや別の担当者のシートにも簡単に展開できます。
「複数のマスタを使い分けたい」「列ごとに違うリストを設定したい」など、 さらに複雑なパターンも作成できますので、 興味があれば応用編としてまたご紹介したいと思います。
コメント