Excel VBAで既存データの入力規則を一括変更する方法（複数シート対応）

Excel VBAで既存のセルに設定されている「データの入力規則」を、同じ入力規則が設定されたすべてのセルにまとめて変更したい場合、VBAでループ処理を用いる方法があります。特に300シート以上あるブックでは、手動では困難なため、マクロで一括変更するのが効率的です。

入力規則変更の基本構文

VBAでは、対象のRangeオブジェクトのValidationプロパティを使って入力規則を設定します。既存の入力規則を変更する場合は、まずDeleteメソッドで既存のValidationを削除し、Addメソッドで新しいルールを設定します。

例: Range("A1").Validation.Delete で既存ルールを削除し、Range("A1").Validation.Add Type:=xlValidateList, Formula1:="Yes,No" でリスト入力規則を再設定します。

複数シート・複数セルへの適用

すべてのシートで同じ入力規則を変更するには、Sheetsコレクションでループを回します。また、変更したいセル範囲を指定し、ValidationのDeleteとAddを適用します。

例:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
  With ws.Range("A1:A100")
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, Formula1:="Yes,No"
  End With
Next ws

「同じ入力規則が設定されたすべてのセルに変更を適用する」に相当する処理

Excel UIのチェックボックス「同じ入力規則が設定されたすべてのセルに変更を適用する」は、VBAでは、範囲を指定してValidationを操作することで再現できます。つまり、同じ入力規則があるセル全体を選択するか、VBAで対象範囲を定義して一括変更します。

ポイントは、DeleteとAddを使う順序です。Deleteで既存のValidationを削除した後に、新しいValidationをAddします。これにより、全セルに新ルールが適用されます。

注意点とベストプラクティス

・広範囲のセルや大量のシートを操作する場合、マクロ実行前に必ずバックアップを取ること。
・Deleteを使用すると元の入力規則が完全に削除されるため、必要に応じて既存設定を保存すること。
・セル範囲を正しく指定しないと、意図しないセルの入力規則が変更される可能性があります。

まとめ

Excel VBAで既存データの入力規則を一括変更するには、Validation.Deleteで既存ルールを削除し、Validation.Addで新しいルールを設定します。複数シートや範囲に適用することで、UIの「同じ入力規則が設定されたすべてのセルに変更を適用する」に相当する処理をVBAで実現できます。