セルにドロップダウンリストを作成する方法
セルにある決まった値を入力する場合は、入力規則のリストからリストに登録してある値を
ドロップダウンリストから選択して入力すれば間違いがなく、入力のスピードアップにつながります。
入力規則をVBAで扱うには入力規則のダイアログボックスを表す
Validation【ﾊﾞﾘﾃﾞｨｰｼｮﾝ】オブジェクトを操作します。
Sheet1のA列にドロップダウンリストで入力する列とします。Sheet2のA列にドロップダウンリストに表示するリストを作成します。
ブックを開いたときに自動的にマクロが実行されるように、コードの記述場所を標準モジュールではなく「Microsoft Excel Objects」モジュールのSheet1シート用モジュールのActivate【ｱｸﾃｨﾍﾞｰﾄ】イベントプロシージャに記述します。
イベントプロシージャのWorksheet_Activateプロシージャはシートがアクティブになった時にマクロが実行されます。
Worksheet_Activateプロシージャの使用方法
「sheet2」のA列にドロップダウンリストに表示するリストを作成します。
[Sheet1」のA列にドロップダウンを作成するコードと解説
「Sheet1」のA列のセルにドロップダウンリストから値を選択して、入力をするコード例です。ダウンリストに表示されるデータのリストは「Sheet2」のA列に記述します。
（※別シートのセル範囲を直接参照できるのは「Excel2007」以降です。）
使用する状況に応じてシートや列を変更してください。
Private Sub Worksheet_Activate()
Sheets("Sheet2").Visible = xlHidden
With Range("A:A").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=Sheet2!" & Range("$A$1", Cells(Rows.Count, 1)).Address
.InputTitle = "入力規則"
.InputMessage = "ドロップダウンリストから選択して入力してください"
End With
End Sub
2行目【Sheets(“Sheet2”).Visible = xlHidden】
リストのデータを入力したSheet2をVisible【ﾋﾞｼﾞﾌﾞﾙ】プロパティの値をxlHidden【ｴｯｸｽｴﾙﾋﾄﾞｩﾝ】に設定することでSheet2を非表示にしています。詳細はシートを操作するをご覧ください。
3行目【With Range(“A:A”).Validation】
アクティブシート（Sheet1)のA列全体をRange【ﾚﾝｼﾞ】オブジェクトで指定してその範囲にValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】プロパティを使用して、セルの入力規則を表すValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】オブジェクトを取得し、With【ｳｨｯｼｭ】ステートメントで指定します。
4行目【.Delete】
With【ｳｨｯｼｭ】ステートメントで指定したA列のValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】オブジェクト（入力規則）に対してDelete【ﾃﾞﾘｰﾄ】メソッドを使用して入力規則を削除します。これは、お決まりのようなものでValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】オブジェクト（入力規則）を追加する前には、必ず実行します。入力規則が設定されている範囲に重ねて入力規則を追加するとエラーが発生することが理由です。設定されていないことがわかっていても実行します。
5行目～7行目
【.Add _
Type:=xlValidateList, _
Formula1:=”=Sheet2!” & Range(“$A$1”, Cells(Rows.Count, 1)).Address】
With【ｳｨｯｼｭ】ステートメントで指定したA列のValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】オブジェクト（入力規則）に対してValidation【ﾊﾞﾘﾃﾞｰｼｮﾝ】オブジェクトのAddメソッドで入力規則を追加します。引数のType【ﾀｲﾌﾟ】には、定数のxlValidateList【ｴｯｸｽｴﾙﾊﾞﾘﾃﾞｲﾄﾘｽﾄ】を指定しドロップダウンリストを追加します。引数Formula1【ﾌｫｰﾐｭﾗﾜﾝ】にドロップダウンリストに表示するデータの参照先を指定します。参照先はSeet2のA列全体を参照してAddressプロパティを使用してFormula1で使用できる参照方法にしています。参照は絶対参照で指定します。
Sheet2のA列の参照方法として【”=Sheet2!$A$1:$A$1048576″】この方法でも同じくA列全体を参照できます。
8行目【.InputTitle = “入力規則”】
ValidationオブジェクトのInputTitle【ｲﾝﾌﾟｯﾄﾀｲﾄﾙ】プロパティで入力時のメッセージのタイトルを指定しています。
9行目【.InputMessage = “ドロップダウンリストから選択して入力してください”】
ValidationオブジェクトのInputMessage【ｲﾝﾌﾟｯﾄﾒｯｾｰｼﾞ】プロパティで入力時のメッセージのを指定しています。
入力時メッセージのイメージ
非表示にしたSheet2を再表示する方法
Sheet1のタブの上でマウス右ボタンをクリックして
メニューの中から再表示を選択してSheet2を再表示します。
実行結果
以上で、セルにドロップダウンリストを作成して入力効率UPについての解説を終了します。ありがとうございました。