【Excel VBA】データの入力規則を一括設定させるマクロ（Range指定対応）

この記事でできること

Excel VBAを使用して、指定したセル範囲（Area）に対して、別のセル範囲（List）をリスト元としたデータの入力規則を効率よく一括設定することができます。Rangeオブジェクトを引数に取るため、柔軟なセル指定が可能です。

想定環境・前提条件

• Microsoft Excel 2010以降（Microsoft 365推奨）
• VBAの実行権限および開発タブが有効な環境

完全なコード例

Sub test()
    ' D4:D15に入力規則を設定し、リストの参照元としてI2:I5を指定する例
    Call 入力規則の設定(Me.Range("D4:D15"), Me.Range("I2:I5"))
End Sub

' 入力規則の設定
' Area: 入力規則を設定するセル範囲
' List: リストの選択肢となるセル範囲
Sub 入力規則の設定(ByRef Area As Range, ByRef List As Range)
    With Area.Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertWarning, _
             Operator:=xlBetween, _
             Formula1:="=" & List.Address(External:=True)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = False
    End With
End Sub

使い方とコードのポイント

呼び出し元のプロシージャ（testなど）から、対象範囲とリスト範囲をRangeオブジェクトで渡します。

ポイントとなるのはValidationプロパティの指定方法です。

Formula1:="=" & List.Address(External:=True)

ここで指定するアドレスの先頭には=を付与する必要があります。また、別シートを参照する場合などを考慮し、List.Address(External:=True)を使用するとシート名やブック名を含めた絶対参照になりエラーを防ぎやすくなります。

典型的な失敗例・エラー

エラーが発生する場合の確認ポイント：

• Listの範囲が別シートを指している場合、Addressプロパティにシート名が含まれていないと「値りストは、区切られたリストであるか、または単一の行や列でなければなりません。」というエラーになることがあります。必要に応じてExternal:=Trueを利用してください。
• Areaが単一セルではなく複数エリアに分かれている場合（マルチエリア）、Validationの設定でエラーになることがあるため、連続したセル範囲を指定してください。

参考情報

Microsoft Learn: Validation オブジェクト (Excel)