[Excel VBA] ValidationList 入力規則

設定の仕方の基本

Excel2013以降
データ>データツール>データの入力規則
わかりにくくなっている

マクロで設定して、修正することをやってみる。

vb.net

Sub 条件付き書式を最初に設定()

Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="ガンダム,ガンキャノン,ガンタンク"
.IgnoreBlank = True '[IgnoreBlank](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-ignoreblank-property-excel)空白を無視するか。IgnoreBlank プロパティが True に設定されていて、セルが空白、あるいは MinVal または MaxVal プロパティの参照セルが空白のとき、セル データは有効であると見なされます。
.InCellDropdown = True'[InCellDropdown](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-incelldropdown-property-excel)入力規則で可能な値を含むドロップダウン リストを表示します
.InputTitle = "" '[InputTitle](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-inputtitle-property-excel)
.ErrorTitle = ""
.InputMessage = "" '[InputMessage](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-inputmessage-property-excel)
.ErrorMessage = "" '[ErrorMessage](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-errormessage-property-excel)
.IMEMode = xlIMEModeNoControl
.ShowInput = True '[ShowInput](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-showinput-property-excel)
.ShowError = True '[ShowError](https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/validation-showerror-property-excel)
End With
Range("A1").Select
End Sub

変更

実際は変更ではなく、一度削除してからADDされている。
Modifyメソッドは使われていない。

vb.net

Sub 設定した条件付き書式を変更する()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim shp
For Each shp In ws.Shapes
Debug.Print shp.Name, shp.Type
Next
Set shp = ws.Shapes("Drop Down 6")
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select '指定された条件を満たしているすべてのセル ( Range オブジェクト) を返します。 xlCellTypeSameValidation 同じ条件の設定が含まれているセル -4175
With Selection.Validation
ActiveSheet.Shapes("Drop Down 4").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation '[セルの入力規則を設定する（Validationオブジェクト）Relievf](https://www.moug.net/tech/exvba/0050121.html)
'リストにある値のみを入力可能にするには、引数Typeに「xlValidateList」を指定し、引数Formura1にリストを指定します。
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=""ガンダム,ガンキャノン,ガンタンク,百式" '入力規則を設定するときは、Deleteメソッドで削除してからAddメソッドで設定する。
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl 'IMEモードのセット
.ShowInput = True
.ShowError = True
End With
End Sub

Validation.Type プロパティ (Excel)
セル範囲に設定されている入力規則の種類を表す長整数型 ( Long ) の値を返します。 XlDVType クラスの定数を使用します。

名前	値	説明
xlValidateCustom	7データは、任意の式を使用して検証されます。
xlValidateDate	4	データ値。
xlValidateDecimal	2	数値。
xlValidateInputOnly	0	ユーザーが値を変更した場合にのみ検証が行われます。
xlValidateList	3	指定された一覧に値が存在する必要があります。
xlValidateTextLength	6	テキストの長さ。
xlValidateTime	5	時刻値。
xlValidateWholeNumber	1	数値全体。

Validation.AlertStyle プロパティ (Excel入力規則でのエラーのスタイルを返します。値の取得のみ可能です。 XlDVAlertStyle クラスの定数を使用します。
エラーのスタイルを特定の範囲に設定する場合は、 Add メソッドを使用します。既に入力規則が設定されている範囲の場合は、 Modify メソッドを使用してエラーのスタイルを変更します。
XlDVAlertStyle 検証中に表示されるメッセージ ボックスで使用されるアイコンを指定します。

名前	値	説明
xlValidAlertInformation	3	情報アイコン
xlValidAlertStop	1	中止アイコン
xlValidAlertWarning	2	警告アイコン

Validation.Formula1 プロパティ (Excel)条件付き書式または入力規則に使用されている値またはオブジェクト式を返します。定数値、文字列値、セル参照、または数式を使用できます。値の取得のみ可能です。文字列型 ( String ) の値を使用します。
Validation.Formula2 プロパティ (Excel) 条件付き書式または入力規則の 2 番目の部分に使用されている値またはオブジェクト式を返します。 Operator プロパティに xlBetween または xlNotBetween が設定されている場合にだけ、このプロパティを使用できます。定数値、文字列値、セル参照、または数式を指定できます。値の取得のみ可能です。文字列型 ( String ) の値を使用します。
Validation.Add メソッド (Excel)
expression . Add( Type , AlertStyle , Operator , Formula1 , Formula2 )

入力規則の種類	引数
xlValidateCustom 引数	Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、データ入力が有効な場合は True に評価され、データ入力が無効な場合は False に評価される式を指定する必要があります。
xlInputOnly 引数	AlertStyle 、 Formula1 、または Formula2 が使用されます。
xlValidateList 引数	Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、コンマで区切った値の一覧またはこの一覧へのシート参照を指定する必要があります。
xlValidateWholeNumber 、 xlValidateDate 、 xlValidateDecimal 、 xlValidateTextLength 、または xlValidateTime 引数	引数 Formula1 と引数 Formula2 のどちらかを指定する必要があります。両方を指定することもできます。

Validation.Modify メソッド (Excel)セル範囲のデータの入力規則を変更します。
式 . Modify( Type, AlertStyle, Operator, Formula1, Formula2 )
Type:入力規則の種類
AlertStyle 入力規則のメッセージのスタイル
Operator 演算子
Formula1 条件式の最初
Formula2 2番目の部分

Modify メソッドに必要な引数の種類を表す定数と必要な引数は次のとおりです。

入力規則の種類	引数
xlInputOnly引数	AlertStyle 、 Formula1 、および Formula2 は使用しません。
xlValidateCustom	引数 Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、データ入力が有効な場合は True に評価され、データ入力が無効な場合は False に評価される式を指定する必要があります。
xlValidateList 引数	Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、コンマで区切った値の一覧またはこの一覧へのシート参照を指定する必要があります。
xlValidateDate 、 xlValidateDecimal 、 xlValidateTextLength 、 xlValidateTime 、または xlValidateWholeNumber	Formula1 または Formula2 の一方、または両方を指定する必要があります。

vb.net

Range("e5").Validation _
.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=$A$1:$A$10"

Validation.ShowInput プロパティ (Excel)True の場合、ユーザーが入力規則のセル範囲を選択すると、入力規則での入力メッセージが必ず表示されます。
Validation.Operator プロパティ (Excel)入力規則の演算子を表す長整数型 ( Long ) の値を取得、または設定します。
XlFormatConditionOperator 列挙 (Excel)数式をセル内の値に対して比較するため、または xlBetween および xlNotBetween の場合 2 つの数式を比較するために使用する演算子を指定します。
数式をセル内の値に対して比較するため、または xlBetween および xlNotBetween の場合 2 つの数式を比較するために使用する演算子を指定します。

名前	値	説明
xlBetween	1	間。2
xlEqual	3	等しい
xlGreater	5	次の値より大きい
xlGreaterEqual	7	以上
xlLess	6	次の値より小さい
xlLessEqual	8	以下
xlNotBetween	2	次の値の間以外。2つの数式が指定されている場合にのみ使用できます。
xlNotEqual	4	等しくない

Validation.ShowError プロパティ (Excel)Trueで無効な入力すると必ずエラーを表示する。
Validation.ErrorMessage プロパティ (Excel)入力規則でのエラー メッセージを取得または設定します。値の取得および設定が可能です。文字列型 ( String ) の値
Validation.InCellDropdown プロパティ (Excel)入力規則の種類が xlValidateList でかつTrue の場合、入力規則で可能な値を含むドロップダウン リストを表示します。値の取得および設定が可能です。ブール型 ( Boolean ) の値を使用します。
有効なデータが含まれるセル範囲を指定する場合、 Validation オブジェクトの Add メソッドまたは Modify メソッドの引数 Formula1 を使用します。
セル E5 に入力規則を設定します。セル範囲 A1:A10 にはそのセルに有効な値が含まれており、セルにはこれらの値を含むドロップダウン リストが表示されます。

With Range("e5").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$A$1:$A$10" '絶対番地で指定する
.InCellDropdown = True
End With

入力規則Modifyメソッドの実験

RelifeではModifyメソッドは一つのセルにしか適用できないように記載している。
しかし同じ入力規則の分を訂正することはできる。
これをマクロで表すと、

ActiveCell.SpecialCells(xlCellTypeSameValidation).Select

が得られる。これで式を差し替えられないか試してみた。

試す前提

• まずMSの上記のようにE5に入力規則を入れるが、これをE5：AV100にする
• 最初はA1:A10の範囲のa-iの文字を入れるようにする
• 次にその範囲の入力規則をモビルスーツ等のリストにする。
• そのリストは入力規則自体が持っている。

コード

Sub testValidationListModify()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim r As Range
Dim icol As Long, irow As Long, lastcol As Long, lastRow As Long, i As Long
Dim ar
ar = Split("a,b,c,d,e,f,g,h,i,j,k", ",")
irow = 1
For i = LBound(ar) To UBound(ar)
ws.Range("A" & irow).Value = ar(i)
irow = irow + 1
Next i
With Range("E5:Av100").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$A$1:$A$10" '絶対番地で指定する
.InCellDropdown = True
End With

ws.Range("E5").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Modify Type:=xlValidateList, Operator:= _
xlBetween, Formula1:="ガンダム,ガンキャノン,ガンタンク,ザク,ズゴック,ドム,ジム,ボール,ジオング,グフ,ギャン"
End With
End Sub

結果 - 成功

• Excel2016で試した結果、式は変更された。
• E5が結合していてもActivecellからはじめてよい。
• 特にRangeになるということもない。

まとめ

1. 入力規則を呼び出すときの手順がExcel2013以降で異なる。
2. 範囲が同じであればModifyメソッドでまとめて書き換えることも可能。