みなさまこんにちは、ノグチです。
エクセルの入力規則の一つであるドロップダウンリストは、ユーザーに特定の値しか入力させたくない！という時に設定しておくと便利ですよね。
ただ、予めリストに入力する値や、リストに表示したいセル範囲を指定しておかないといけなかったりして、入力値によってリストの内容を変えたい！というような場合には困ってしまうこともあるかもしれません。
VBAで入力規則のドロップダウンリストを作成できれば、シートやブック内の入力値によってリストの内容を可変にできるかもしれない…
また、前回記事でご紹介したように、セルの値から検索した値をドロップダウンリストで表示出来たら…
そうお考えの方のために、今回はVBAで入力規則のドロップダウンリストを作成する方法をご紹介します！
前回記事では、エクセルのChangeイベントを使って、セルに入力した得意先名の一部から、マスタシートにある得意先の正式名称を検索して取得する、という方法をご紹介していますので、併せてご覧ください。
ValidationオブジェクトのAddメソッドで指定したセル範囲に入力規則を作る
エクセルの入力規則を作るには、ValidationオブジェクトのAddメソッドを使います。
Validationオブジェクトは、まずRangeオブジェクトのValidationプロパティでセル範囲の入力規則をValidationオブジェクトとして取得します。
そのValidationオブジェクトに対して、以下のようにAddメソッドで入力規則を作成します。
そしてAddメソッドのパラメータを指定していきす。
Addメソッドのパラメータ
Addメソッドのパラメータには、例えば下記のようなものがあります。
Type
入力規則の種類を指定するパラメータです。
入力規則の種類を指定しなければならないため、当然ながら必須のパラメータです。
例えばこんな値を指定します。
| 指定する値 | 入力規則の種類 |
|---|---|
| xlValidateList | ドロップダウンリスト |
| xlValidateWholeNumber | 整数 |
| xlValidateDate | 日付 |
今回はドロップダウンリストを作成したいので、xlValidateListを指定します。
AlertStyle
入力規則で指定した値以外がセル範囲に入力された場合に表示するメッセージの種類と、メッセージを表示した時の動作を指定します。
表示するメッセージの値として指定するのは下記のいずれかです。
| 指定する値 | 表示されるメッセージ | 動作 |
|---|---|---|
| xlValidAlertStop |  |
入力規則で指定した通りの値や型しか入力できない |
| xlValidAlertWarning |  |
警告メッセージが表示されるが、入力規則で指定した値や型以外の値が入力できる |
| xlValidAlertInformation |  |
入力規則で指定した通りの値や型以外が入力されていることをメッセージで知らせる。入力値に対して制限はない。 |
Formula1
入力規則のドロップダウンリストに表示したい文字を、255文字以内で指定します。
TypeパラメータでxlValidateListを指定している場合は、必ず指定する必要があります。
ドロップダウンリスト形式の入力規則を作成するコードの例
上のメソッドとパラメータをコードにしてみると、こんな感じになります。
ActiveSheet.Range("B5").Validation.Add _
Type:=xlValidateList, _
AlertStyle:=3, _
Formula1:="a,1,b,2,c,3"
あるいは、Validationオブジェクトには色々なプロパティやメソッドがありますので、それらを指定した時のコードの見やすさを考慮して、こうしておきましょう。
With ActiveSheet.Range("B5").Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="a,1,b,2,c,3"
End With
Deleteメソッドで既存の入力規則を削除しておく
Addメソッドを使う時に気を付けたいのが、すでに入力規則があるセル範囲に対してAddメソッドを使おうとすると、こんなエラーとなってしまう点です。
VBAのエラーはどうしてこうも毎度分かりにくいのか…
エクセルツール使用中に複数回Addメソッドのコードを通る場合は、Deleteメソッドで先に作成した入力規則を削除しておくようにしましょう。
Modifyメソッドを使う方法もありますが、こちらのメソッドは機会があれば別途ご紹介します。
Deleteメソッドの記述方法はこちら。
Addメソッドの前にDeleteメソッドを差し込む
Deleteメソッドを上のコードに差し込むと、こんなコードになります。
With ActiveSheet.Range("B5").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="a,1,b,2,c,3"
End With
B5セルの入力規則を一旦削除して、同じくB5セルにドロップダウンリストの入力規則を作成する、という動作になります。
では、さっそくこのコードを動かしてみましょう！
動作確認
シートを開いて、上のVBAコードを実行してみると…
この通り、B5セルに入力規則のドロップダウンリストができていますね。
最後に
今回は、入力規則のドロップダウンリストをVBAで表示する方法をご紹介しました。
次回は、今回ご紹介したドロップダウンリストを表示する方法と、前回ご紹介した、セルに入力した値から一部検索して得意先の正式名称を取得する方法を組み合わせて、入力した値から複数の検索候補をドロップダウンリストで表示する方法をご紹介していきます！
それでは、最後までお読みいただきありがとうございました！
連載目次：エクセルVBAのイベントを使ってもっと便利なツールにしよう！
エクセルVBAでリストの重複を排除する方法として、Dictionaryオブジェクトを使った重複排除の方法をご紹介しています。
- 【エクセルVBA】イベントを使ってもっと便利なツールにしてみよう！WorksheetオブジェクトのChangeイベント
- 【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法
- 【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法
- 【エクセルVBA】Changeイベントと組み合わせてもっと便利に！検索にヒットした値をドロップダウンリストに表示する方法
