【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法


exce,dropdownList

みなさまこんにちは、ノグチです。

エクセルの入力規則の一つであるドロップダウンリストは、ユーザーに特定の値しか入力させたくない!という時に設定しておくと便利ですよね。

ただ、予めリストに入力する値や、リストに表示したいセル範囲を指定しておかないといけなかったりして、入力値によってリストの内容を変えたい!というような場合には困ってしまうこともあるかもしれません。

VBAで入力規則のドロップダウンリストを作成できれば、シートやブック内の入力値によってリストの内容を可変にできるかもしれない…

また、前回記事でご紹介したように、セルの値から検索した値をドロップダウンリストで表示出来たら…

そうお考えの方のために、今回はVBAで入力規則のドロップダウンリストを作成する方法をご紹介します!

前回記事では、エクセルのChangeイベントを使って、セルに入力した得意先名の一部から、マスタシートにある得意先の正式名称を検索して取得する、という方法をご紹介していますので、併せてご覧ください。

【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法
WorksheetオブジェクトのChangeイベントと、ApplicationオブジェクトのEnableEventsプロパティを使って、セルに得意先名の一部を入力したら、マスタシートから正式名称を取得して入力セルに返す、というコードをご紹介しています。面倒かつ間違い易い入力作業は、出来るだけ簡略化したいですね。

ValidationオブジェクトのAddメソッドで指定したセル範囲に入力規則を作る

エクセルの入力規則を作るには、ValidationオブジェクトのAddメソッドを使います。

Validationオブジェクトは、まずRangeオブジェクトのValidationプロパティでセル範囲の入力規則をValidationオブジェクトとして取得します。

Rangeオブジェクト.Validation

そのValidationオブジェクトに対して、以下のようにAddメソッドで入力規則を作成します。

Validationオブジェクト.Add
またはこちらでもOK。
Rangeオブジェクト.Validation.Add

そしてAddメソッドのパラメータを指定していきす。

Addメソッドのパラメータ

Addメソッドのパラメータには、例えば下記のようなものがあります。

Type

入力規則の種類を指定するパラメータです。

入力規則の種類を指定しなければならないため、当然ながら必須のパラメータです。

例えばこんな値を指定します。

指定する値 入力規則の種類
xlValidateList ドロップダウンリスト
xlValidateWholeNumber 整数
xlValidateDate 日付

今回はドロップダウンリストを作成したいので、xlValidateListを指定します。

Type:=xlValidateList

AlertStyle

入力規則で指定した値以外がセル範囲に入力された場合に表示するメッセージの種類と、メッセージを表示した時の動作を指定します。

AlertStyle:=表示するメッセージの種類

表示するメッセージの値として指定するのは下記のいずれかです。

指定する値 表示されるメッセージ 動作
xlValidAlertStop 入力規則,エラー,メッセージ,xlValidAlertStop 入力規則で指定した通りの値や型しか入力できない
xlValidAlertWarning 入力規則,メッセージ,xlValidAlertWarning 警告メッセージが表示されるが、入力規則で指定した値や型以外の値が入力できる
xlValidAlertInformation 入力規則,メッセージ,xlValidAlertInformation 入力規則で指定した通りの値や型以外が入力されていることをメッセージで知らせる。入力値に対して制限はない。

Formula1

入力規則のドロップダウンリストに表示したい文字を、255文字以内で指定します。

TypeパラメータでxlValidateListを指定している場合は、必ず指定する必要があります。

Formula1:=”指定したい値1, 指定したい値2, …”

ドロップダウンリスト形式の入力規則を作成するコードの例

上のメソッドとパラメータをコードにしてみると、こんな感じになります。

あるいは、Validationオブジェクトには色々なプロパティやメソッドがありますので、それらを指定した時のコードの見やすさを考慮して、こうしておきましょう。

Deleteメソッドで既存の入力規則を削除しておく

Addメソッドを使う時に気を付けたいのが、すでに入力規則があるセル範囲に対してAddメソッドを使おうとすると、こんなエラーとなってしまう点です。

ドロップダウンリスト,入力規則,重複エラー

VBAのエラーはどうしてこうも毎度分かりにくいのか…

エクセルツール使用中に複数回Addメソッドのコードを通る場合は、Deleteメソッドで先に作成した入力規則を削除しておくようにしましょう。

Modifyメソッドを使う方法もありますが、こちらのメソッドは機会があれば別途ご紹介します。

Deleteメソッドの記述方法はこちら。

Validationオブジェクト.Delete
又はこう。
Rangeオブジェクト.Validation.Delete

Addメソッドの前にDeleteメソッドを差し込む

Deleteメソッドを上のコードに差し込むと、こんなコードになります。

B5セルの入力規則を一旦削除して、同じくB5セルにドロップダウンリストの入力規則を作成する、という動作になります。

では、さっそくこのコードを動かしてみましょう!

動作確認

シートを開いて、上のVBAコードを実行してみると…

ドロップダウンリスト,入力規則,動作確認

この通り、B5セルに入力規則のドロップダウンリストができていますね

最後に

今回は、入力規則のドロップダウンリストをVBAで表示する方法をご紹介しました。

次回は、今回ご紹介したドロップダウンリストを表示する方法と、前回ご紹介した、セルに入力した値から一部検索して得意先の正式名称を取得する方法を組み合わせて、入力した値から複数の検索候補をドロップダウンリストで表示する方法をご紹介していきます!

【エクセルVBA】Changeイベントと組み合わせてもっと便利に!検索にヒットした値をドロップダウンリストに表示する方法
エクセルのChangeイベントと、Validationオブジェクトを用いたドロップダウンリストを作成する方法を組み合わせて、セルに入力した値をマスタシートから検索し、検索にヒットした値を入力したセルのドロップダウンリストに表示させる方法をご紹介しています。この方法を使えば、入力作業が楽になるかもしれません。

それでは、最後までお読みいただきありがとうございました!

連載目次:エクセルVBAのイベントを使ってもっと便利なツールにしよう!

エクセルVBAでリストの重複を排除する方法として、Dictionaryオブジェクトを使った重複排除の方法をご紹介しています。

  1. 【エクセルVBA】イベントを使ってもっと便利なツールにしてみよう!WorksheetオブジェクトのChangeイベント
  2. 【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法
  3. 【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法
  4. 【エクセルVBA】Changeイベントと組み合わせてもっと便利に!検索にヒットした値をドロップダウンリストに表示する方法

The following two tabs change content below.

ノグチ

元製造子会社SE、ERPパッケージシステムコンサルタント(販売管理、購買管理、生産管理、設備管理他)。 業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁ、楽にお仕事してもらえたらいいなぁと日々夢見ております。

コメント