今回はExcel機能の入力規則で、2つの連動したプルダウンリストを設定する方法をご説明します。
今回の方法はExcelの関数を使わずに、リストの作成をします。
Excelで入力規則を操作しても、データ構造等をしっかり組み立てる事で、2つまでの連動であれば比較的簡単に設定する事は出来ますが、それをVBAで作成します。
VBAで入力規則の基本的な操作方法と、データ構成も併せてご説明します。
Excelで入力規則を使用してプルダウンリストを作成する方法は「入力規則の基本説明と動的にプルダウンリストを作成する方法」をご覧ください。
また、VBAで入力規則の作成方法や、3つ以上の連動するリストの作成方法については、下記記事をご覧ください。
1.入力規則のリスト用のデータ構成
VBAで入力規則の連動したリストを作成するに2つまでは難しい構成は必要ありません。
それぞれの項目の階層が紐づくようにデータの一覧を作成します。
次のようなメーカーと商品が紐づくような一覧を用意しました。
そしてH4がH2で指定された文字列と連動するプルダウンリストを作成します。
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.プルダウンリストの作成
まずは絞り込まれていないプルダウンリストを作成します。
そもそも「何も選択していない」状態では、どちらも全てのリストが選択できる状態である必要があります。
Sub List_Set()
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
Dim DicKey As Variant
Dim ListStr As String
With ActiveSheet
'既存の入力規則を削除
.Range("H2,H4").Validation.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 2).End(xlUp).Row
Set CheckDic = CreateObject("Scripting.Dictionary")
'=========1つ目のリスト作成=========
For n = 2 To 5 '項目数4列をループ
Myval = Cells(2, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
Next n
DicKey = CheckDic.keys 'Keyを変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
.Cells(2, 8).Validation.Add Type:=xlValidateList, Formula1:=ListStr
'=========2つ目のリスト作成=========
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 2).End(xlUp).Row
Set CheckDic = CreateObject("Scripting.Dictionary") 'Dictionaryを初期化
For n = 2 To 5 '項目数3列をループ
For i = 3 To MaxRow '3行目から最終行までループ
Myval = Cells(i, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
Next i
DicKey = CheckDic.keys 'Keyを変数に格納
Next n
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
'入力規則を設定
.Cells(4, 8).Validation.Add Type:=xlValidateList, Formula1:=ListStr
End With
End Sub
この様にすべてのリストが追加されました。
コードの説明です。
基本的にはコメントに何をしているか記載しています。
まずはお決まりの「.Range(“H2,H4”).Validation.Delete」で入力規則を一度削除します。
離れたセルを複数選択する場合は上記の様にカンマで区切ってセルを指定します。
まずは1つ目のリスト作成としてB3～E3まで列をループしてリストの文字列を格納します。
サンプルデータは重複していませんが、重複していないリストを作成するようにDictionaryを使用します。
「Set CheckDic = CreateObject(“Scripting.Dictionary”)」で宣言します。
「Myval = Cells(2, n).Value」で該当文字列を格納してDictionaryのKeyに登録していきます。
「DicKey = CheckDic.keys」でKeyを変数に格納して、「ListStr = Join(DicKey, “,”)」で変数をリスト用にカンマ区切りで統合して格納します。
「.Cells(2, 8).Validation.Add Type:=xlValidateList, Formula1:=ListStr」でH2にリストを追加します。
2つ目のリストも同様の方法で、行と列のループで一覧の中の商品をすべて格納して、最後にリストに追加します。
5.連動するプルダウンリストの作成
次に1つ目のプルダウンリストが選択された場合に、関連付く2つ目のリストを作成する方法です。
こちらはH2に指定された文字列をキーワードにして、列を取得してその該当列のリストを取得します。
まず2つ目のリストを作成するコードの前に、1つ目のリストが変更されたら2つ目のリストを絞り込むためのコードが実行されるイベントを用意します。
Worksheet_Changeを組み込む
H2のセルが変更されたらH6のリストを変更するためのコードが実行されるように、「Worksheet_Change」にイベントを記載します。
今回はSheet1にリストを作成するので、Sheet1モジュールにコードを記述します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 8 Then
Call List_Change(Target.Value)
End If
End Sub「If Target.Row = 2 And Target.Column = 8 Then」で2行目、8列目のセルが変更されたときのみ実行されるように指定します。
「Call List_Change(Target.Value)」でこれから作成する、H4のリストを作成するコードを呼び出しますが、1つ目のリストで指定されたここで値を渡します。
「Target.Value」が選択したH2の値です。
2つ目のリストを作成する
次は標準モジュールに1つ目のリストに該当する列を取得して、その列にあるリスト一覧を格納してプルダウンリストに登録します。
Sub List_Change(ByVal GetStr As String)
Dim CheckDic As Object
Dim MaxRow As Long
Dim TargetCol As Long
Dim n As Long
Dim Myval As String
Dim DicKey As Variant
Dim ListStr As String
With ActiveSheet
If GetStr = "" Then '1つ目のリストが削除された場合はSetしなおす処理
Call List_Set
Else
'2つ目の入力規則を削除
.Range("H4").Validation.Delete
'=========1つ目のリストを取得=========
For n = 2 To 5 '項目数4列をループ
If .Cells(2, n) = GetStr Then
TargetCol = n
Exit For
End If
Next n
'=========2つ目のリスト作成=========
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 2).End(xlUp).Row
Set CheckDic = CreateObject("Scripting.Dictionary")
For n = 3 To MaxRow '3行目から最終行までループ
Myval = Cells(n, TargetCol).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
Next n
DicKey = CheckDic.keys 'Keyを変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
'入力規則を設定
.Cells(4, 8).Validation.Add Type:=xlValidateList, Formula1:=ListStr
End If
End With
End Subコードの説明です。
「List_Change(ByVal GetStr As String)」の(ByVal GetStr As String)は先ほどの「Worksheet_Change」で取得した1つ目のリストの文字列を受け取るための宣言です。
「If GetStr = “” Then」で、もしリスト1つ目が削除された場合はエラーとなるため、最初に作った全てのリストを設定するコードを呼び出す処理です。
取得した文字列が空白ではない場合はリストの作成を行います。
「.Range(“H4”).Validation.Delete」でH4のリストを一旦削除します。
列をループして、取得した文字列と一致する列を取得します。
この取得した列を「TargetCol = n」で格納して、2つ目のセルの列に使用します。
「Myval = Cells(n, TargetCol).Value」で該当列の文字列をループで最終行までDictionaryに格納します。
最後は同じです。
「DicKey = CheckDic.keys」でKeyを変数に格納して、Join関数でリスト用のカンマ区切りの文字列を作り、最後にH4にリストとして登録します。
無事H2にBメーカーと指定するとC列の関連したリストのみ登録出来ました。
6.ファイルを開いた時にリストを作成する
一番最初にリストを追加する場合に、ファイルを開いた時にリストを作成するように組み込むことで、リストが表示されないという事を避ける事ができます。
「ThisWorkbook」モジュールに次のように記述するだけです。
Private Sub Workbook_Open()
Call List_Set
End Subファイルを開いた時に自動で実行する方法の詳細については「ファイルを開く時にマクロを実行する方法」をご覧ください。
7.サンプルファイルダウンロード
今回は少し複雑な仕組みとなりましたので、今回使用したサンプルデータとサンプルコードをダウンロードできるようにします。
