こんにちは！Excel VBA講師のハルトです。
日々の業務で、「ユーザーが勝手に変なデータを入力してしまい、集計が狂った」「プルダウンリストで選択させたいのに、直接入力されて困る」といったトラブルに悩まされたことはありませんか？
Excelの「データの入力規則」は非常に強力な機能ですが、手動で設定するのは面倒ですし、シートが増えるごとに管理も大変になります。そこで今回は、VBAを使って「Validationオブジェクト」を自在に操り、入力ミスを未然に防ぐプロレベルのテクニックを解説します。
1. Validationオブジェクトの基本構造を理解する
まず、VBAで入力規則を扱うには、対象となるRangeオブジェクトの「Validationプロパティ」にアクセスします。
基本的な書き方は以下の通りです。
With Range(“A1″).Validation
.Delete ‘ 一旦既存の規則を削除
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=”A,B,C”
End With
この「Validationオブジェクト」は、セルに対して「何を（Type）」「どんな制限で（Operator）」「エラーが出た時にどうするか（AlertStyle）」を定義するものです。
2. 入力規則を設定する際の「お約束」
VBAで入力規則を設定する際、必ず守るべきステップが2つあります。
1. **Deleteメソッドを呼ぶ**：既存の規則が設定されているセルに重ねて設定しようとすると、エラーが発生します。まずは`.Delete`でクリーンな状態にしましょう。
2. **適切なType（種類）を選ぶ**：
– `xlValidateList`：リスト選択（プルダウン）
– `xlValidateWholeNumber`：整数のみ
– `xlValidateDecimal`：小数点を含む数値
– `xlValidateDate`：日付
– `xlValidateTextLength`：文字数制限
3. 実践：プルダウンリストを動的に作成する
実務で最も頻繁に使うのが「リスト選択」です。静的な値（”A,B,C”）だけでなく、セル範囲を参照してリストを作る方法を覚えましょう。
Sub SetDropDownList()
Dim targetRange As Range
Set targetRange = Range(“B2:B10″)
With targetRange.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=”=$E$1:$E$5” ‘ E1からE5のリストを参照
.InCellDropdown = True ‘ プルダウンを表示する
.InputTitle = “入力案内”
.InputMessage = “リストから選択してください。”
End With
End Sub
ポイントは`.InCellDropdown = True`です。これがないと、リスト形式なのにプルダウン矢印が表示されません。また、`InputTitle`や`InputMessage`を設定することで、セルを選択した時にツールチップを表示させ、ユーザーへの親切な案内を実装できます。
4. 数値の範囲制限とエラーメッセージのカスタマイズ
次に、売上金額や年齢など、「特定の範囲内しか受け付けない」設定です。ここで重要なのが「エラー警告」の出し方です。
Sub SetNumberValidation()
With Range(“C2″).Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=”1″, _
Formula2:=”100”
.ErrorTitle = “入力エラー”
.ErrorMessage = “1から100までの数値を入力してください。”
End With
End Sub
`AlertStyle`には3つの種類があります。
– `xlValidAlertStop`：入力を完全に禁止（最も推奨）
– `xlValidAlertWarning`：警告を出すが、入力を継続可能
– `xlValidAlertInformation`：情報として通知するのみ
「絶対に誤入力を許さない」業務フローであれば、必ず`xlValidAlertStop`を選択してください。
5. 高度なテクニック：別シートのリストを参照する
実は、Validationオブジェクトの`Formula1`には、別シートの範囲を直接指定するとエラーになるというExcelの仕様（バグに近い挙動）があります。これを回避するには「名前の定義」を使うのが鉄則です。
Sub SetDynamicListFromOtherSheet()
‘ 事前に名前の定義をしておくか、VBAで名前を定義する
ThisWorkbook.Names.Add Name:=”CategoryList”, RefersTo:=”=Sheet2!$A$1:$A$10″
With Range(“D2″).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=”=CategoryList”
End With
End Sub
このように、名前付き範囲を経由することで、シートをまたいだ柔軟なリスト作成が可能になります。
6. 運用上の注意点とトラブルシューティング
最後に、ベテランとしてアドバイスしたい「運用上の注意点」を3つ挙げます。
1. **保護されたシートへの書き込み**：
シートが保護されていると、VBAであっても入力規則の変更は失敗します。`ActiveSheet.Unprotect`を忘れずに。
2. **コピー＆ペーストの罠**：
ユーザーが別の場所からデータをコピーして貼り付けると、入力規則が上書きされて消えてしまうことがあります。これを防ぐには、`Worksheet_Change`イベントで、貼り付けられた範囲に対して再度入力規則を適用する「監視コード」を組むのが理想です。
3. **範囲の可変性**：
リストの項目が増減する場合、`OFFSET`関数を使った動的な名前定義と組み合わせることで、VBAを書き換えずにリストをメンテナンスできるようになります。
まとめ
Validationオブジェクトを使いこなせば、Excelの入力インターフェースは劇的に向上します。ユーザーの「うっかりミス」をVBAで防ぐことは、修正作業という無駄な時間を削減し、チーム全体の生産性を高めることに直結します。
まずは今日紹介したコードを、ご自身の業務ファイルで試してみてください。一度仕組みを作ってしまえば、あとはExcelが勝手にデータの整合性を守ってくれるようになりますよ。
それでは、次回の記事もお楽しみに。ハルトでした！
