今回はExcel VBAでExcel機能の入力規則を操作して、プルダウンリストを作成する方法をご説明します。
セルの範囲をリストに設定する基本的な方法から、配列に格納された値をリストに設定する方法、リストに変更が発生したら自動的に更新する方法までご説明します。
VBAを使わずにExcelで入力規則を作成する方法は「Excel 入力規則でプルダウンリストの作成方法」をご覧ください。
連動するリストのさくせいほうほうについては、下記記事をご覧ください。
1.入力規則とは
操作の説明をする前に、Excelの入力機能について簡単に説明します。
入力規則で出来る事
数値、日付、文字列、文字数など入力する値を制限できます。
ヒントやエラーメッセージ、IME 等も設定できます。
そして、今回使用するプルダウンリストを作成する事ができます。
プルダウンリストを作成するメリット
プルダウンメニューを作成する事で、次のようなメリットがあります。
- 入力する値を統一できる
- 入力するリストを簡単に追加できる
- 自分以外のユーザーの入力制限をする事ができる
- VLOOKUPやCOUNTIF、SUMIF関数と併用で集計を簡易化出来る
上記以外にも多くのメリットがあります。
2.入力規則の削除方法
セルに入力規則を設定する方法を説明する前に、入力規則の削除方法について説明します。
理由は、すでにセルに入力規則が設定されている場合はエラーとなります。
ですので、基本的に入力規則を設定する前に既存の入力規則を削除します。
削除するには設定されているセル(オブジェクト)の「Validationオブジェクト」を操作します。
記述方法は簡単で次のように記述します。
Range("設定されている範囲").Validation.Delete3.入力規則のValidationオブジェクトと引数の説明
セルに入力規則を作るには、「Validationオブジェクト」の「Addメソッド」を使います。
そして引数でそれぞれ詳細を設定します。
次のように記述します。
Validation.Add Type,AlertStyle,Operator,Formula1,Formula2Validationの引数一覧
| 引数名 | 省略 | 説明 |
| Type | 必須 | 入力値の種類を指定します。 |
| AlertStyle | 省略可能 | エラー時の動作を指定します。 |
| Operator | 省略可能 | 指定された値を比較する方法を指定します。 |
| Formula1 | 省略可能 | リストに指定したい値を指定します。 |
| Formula2 | 省略可能 | 引数「Operator」で「xlBetween」もしくは「xlNotBetween」 を 指定した場合に、入力できる範囲の最大もしくは最小を指定します。 |
Typeの定数一覧
「Type」は 入力規則の種類を指定します。
Excelでは次の画面の操作になります。
| 定数 | 数値 | 説明 |
| xlInputOnly | 0 | すべての値 |
| xlValidateWholeNumber | 1 | 整数 |
| xlValidateDecimal | 2 | 小数点 |
| xlValidateList | 3 | リスト指定 |
| xlValidateDate | 4 | 日付指定 |
| xlValidateTime | 5 | 日時指定 |
| xlValidateTextLength | 6 | 文字列(長さ指定) |
| xlValidateCustom | 7 | ユーザ設定 |
AlertStyleの定数一覧
「AlertStyle」はエラー時の動作を指定します。
Excelでは次の画面の操作になります。
| 定数 | 数値 | 説明 |
| xlValidAlertStop | 1 | 停止 |
| xlValidAlertWarning | 2 | 注意 |
| xlValidAlertInformation | 3 | 情報 |
Operatorの定数一覧
「Operator」は指定された値を比較する方法を指定します。
「Type」で「 整数 」、「 小数点 」、「 日付指定 」、「日時指定」、「文字列(長さ指定)」 を選択時に指定する事ができます。
それ以外の「すべての値」、「リスト」、「ユーザ設定」では指定できません。
Excelでは次の画面の操作になります。
4.プルダウンリストの作成
リストの作成方法をサンプルコードを使ってご説明します。
次のようなA列にリストが入力されているサンプルデータを使用します。
Sub Sample1()
'既存の入力規則を削除
With ActiveSheet
.Range("C1").Validation.Delete
'入力規則を設定
.Range("C1").Validation.Add Type:=xlValidateList, Formula1:="=$A$1:$A$6"
End With
End Sub
もちろん1つのセルではなく範囲を指定して設定することも可能です。
次のように記述します。
Sub Sample2()
'既存の入力規則を削除
With ActiveSheet
.Range("C1:C5").Validation.Delete
'入力規則を設定
.Range("C1:C5").Validation.Add Type:=xlValidateList, Formula1:="=$A$1:$A$6"
End With
End Sub5.動的にプルダウンリストを作成
固定のセル範囲をもとにプルダウンリストの作成方法はできましたが、リストが追加されるたびに範囲を再設定するのは非常に非効率ですので、自動でリストの範囲を取得して設定する方法をご説明します。
次の表のサンプルデータを使用します。
Sub Sample3()
Dim ListArray() As Variant
Dim ListStr As String
Dim MaxRow As Long
Dim i As Long
'既存の入力規則を削除
With ActiveSheet
.Range("C1:C5").Validation.Delete
'データを取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
ReDim ListArray(MaxRow - 1)
For i = 1 To MaxRow
ListArray(i - 1) = Cells(i, 1).Value
Next i
ListStr = Join(ListArray, ",")
'入力規則を設定
.Range("C1:C5").Validation.Add Type:=xlValidateList, Formula1:=ListStr
End With
End Sub
コードの説明です。
まずは固定の設定同様に、既存の入力規則の設定を削除します。
「MaxRow = Cells(Rows.Count, 1).End(xlUp).Row」でリストにしたい列の最終行を取得して、配列に格納します。
「ReDim ListArray(MaxRow – 1)」で配列の領域を指定します。
あとはループで配列にリストの値を格納します。
「ListStr = Join(ListArray, “,”)」で格納した配列の要素をJoin関数でカンマ区切りにして結合します。
それを「Formula1:=ListStr」で指定する事でリストが登録されます。
空白も除外されてリストが作成されました。
6.自動的にリストを追加する方法
上記のコードを組めば常に最新のリストが登録される訳ではありません。
常に最新のリストを追加させるにはいくつか方法があります。
ファイルを開いた時に更新する方法
リストを追加して、次回ファイルを開いた時に最新のリストになるようにする場合です。
ファイルを開いた時にマクロを自動的に実行する方法の詳細は「ファイルを開く時にマクロを実行する方法」をご覧ください。
先ほどのSampl3のマクロを使用します。
次のコードを「ThisWorkbookモジュール」に記述します。
Private Sub Workbook_Open()
Call Sample3
End Subこれだけです。
これでファイルが開いた時にリストを作成するSample3のコードが実行され、ファイルを開くたびに最新のリストになります。
セルに変更があるたびに最新のリストに更新する方法
次はもっと更新頻度を増やす場合です。
リストが追加された場合に、毎回すぐ最新のリストになるようにします。
同様に、先ほどのSampl3のマクロを使用します。
次のコードを「Sheetモジュール」に記述します。
もしリストの追加の対象がSheet1であればSheet1モジュールに記述です。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Then
Exit Sub
Else
Call Sample3
End If
End Sub今回のサンプルデータはA列にリストを作成したので、A列のセルに変更が生じた場合にSample3が実行されます。
