リストの入力規則を設定する

VBAでリストの入力規則を設定する方法について
以下の項目を見ていきます。
1. リストの入力規則とは？
2. VBAでリストの入力規則を設定する

1. リストの入力規則とは？

セルの値を"リスト"で入力できる設定（＝入力規則）があります。

リストを作成するには
[データ]タブ-[データの入力規則]をクリックして
表示されるウィンドウで設定をします。

[入力値の種類]を[リスト]にして，
[元の値]に「1,2,3,4,5」と入力します。

[OK]ボタンで入力規則の設定を終了します。
設定後は次の画像のように
ドロップダウンリストが表示されるようなり
このリストからセルに入力する値を選択できます。

[元の値]には
セルの範囲や名前を指定することもできます。

リストの[元の値]に指定する範囲や名前は
単一の行，列の参照でないと設定できません。

[元の値]に列全体を指定すると
値が入っている最後のセル以降の空白セルは無視されます。
リストを設定後
列の内容を変更すると
変更内容に合わせてリストの内容も変更されます。

リストを多数設定したい場合は
リストの項目用のシートを作成して
そのシートの列を参照すれば管理がしやすいです。

デフォルトの設定では
入力規則以外の値が入力されたとき
アラートが表示されて停止する設定になっています。

[エラーメッセージ]タブにある
[無効なデータが入力されたときエラーメッセージを表示する]の
チェックを外すとこのアラートは出なくなり
リストに指定した値以外が入力できるようになります。

2. VBAでリストの入力規則を設定する

次のコードは選択しているセルに
「1,2,3,4,5」のリストを入力規則として設定します。

Sub macro20200601a()
'リストの入力規則を設定する

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, _
            Formula1:="1,2,3,4,5"
        .InCellDropdown = True
        .IMEMode = xlIMEModeNoControl
        .ShowInput = False
        .ShowError = False
    End With

End Sub

入力規則を追加するには
ValidationオブジェクトのAddメソッドを使用します。
Addメソッドを実行する前にDeleteをすることで
既存の入力規則を削除してから新しい入力規則を追加します。
既存入力規則がある状態で追加しようとすると下
記のエラーになります。

実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。

入力規則にはいくつかの種類がありますが
AddメソッドのType引数をxlValidateListにすると
入力規則の種類がリストになります。

AddメソッドのFormula1引数に
リストに表示したい内容を指定します。
上記コードではカンマで区切った文字列を指定しています。

Formula1引数にセルの範囲や名前を指定する場合については
次にいくつかの例を示します。

参照範囲	Formula1引数
同じシート内の範囲O1:O10	Formula1:="=$O$1:$O$10"
別のシートSheet1のA列	Formula1:="=Sheet1!$A:$A"
名前name	Formula1:="=name"
同じシート内の範囲O1:O10をRangeで指定	Formula1:="=" & Range("O1:O10").Address
別のシートSheet1のA列をRangeで指定	Formula1:="=" & Sheets("Sheet1").Name & "!" & Range("A:A").Address

InCellDropdown = Trueにすると
ドロップダウン形式で表示されます。
Falseにするとドロップダウンリストが表示されません。

ShowInput = Falseで
入力規則が設定されたセルを選択したときに表示するメッセージを
表示させないようにします。

ShowError = Falseで
リストで指定した以外の値が入力されたときに
エラーを表示させないようにします。

使用Ver：Win10, Excel For Office365