今回はExcel機能の入力規則で、連動した複数のプルダウンリストを設定する方法をご説明します。
Excelで入力規則を操作しても、データ構造等をしっかり組み立てる事で、2つまでの連動であれば比較的簡単に設定する事は出来ますが、3つ以上となると非常に複雑で難しくなります。
VBAを使うとこの複雑な設定が簡単にできます。
この様なケースのツールはシンプルかつコンパクトに作成すると、とても難しくなりますので、今回は簡単な方法で実装したいと思います。
VBAで入力規則を操作する方法と、データ構成も併せてご説明します。
Excelで入力規則を使用してプルダウンリストを作成する方法は「入力規則の基本説明と動的にプルダウンリストを作成する方法」をご覧ください。
1.入力規則のリスト用のデータ構成
VBAで入力規則の連動したリストを作成するには難しい構成は必要ありません。
それぞれの項目の階層が紐づくようにデータの一覧を作成します。
重複したデータが存在しても構いませんが管理が煩雑になりますので、お勧めしません。
また、極力各階層順に並び替えしておくことでリストの並びが整います。
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列のみを入力規則のリストに設定する方法です。
「1.入力規則のリスト用のデータ構成」で記載したデータを使用します。
サンプルコード
Sub Sample1()
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim Myval As String
Dim DicKey As Variant
Dim ListStr As String
Set CheckDic = CreateObject("Scripting.Dictionary")
With ActiveSheet
'既存の入力規則を削除
.Range("F2").Validatioそn.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To MaxRow '3行目から最終行までループ
Myval = Cells(i, 1).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
Next i
DicKey = CheckDic.keys 'Keyを変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
'入力規則を設定
.Range("F2").Validation.Add Type:=xlValidateList, Formula1:=ListStr
End With
End Sub上記コードを実行すると、重複のないリストがA列に作成できました。
コードの説明
「Set CheckDic = CreateObject(“Scripting.Dictionary”)」でリスト用のDictionaryを宣言します。
既存の入力規則の設定を削除します。
「MaxRow = Cells(Rows.Count, 1).End(xlUp).Row」でリストにしたい列の最終行を取得して、配列に格納します。
「If Not CheckDic.exists(Myval) Then」で重複しないリストを作成します。
「DicKey = CheckDic.keys」で重複のないDictionaryのKeyを変数に格納します。
「ListStr = Join(DicKey, “,”)」で格納したKeyの要素をJoin関数でカンマ区切りにして結合します。
それを「Formula1:=ListStr」で指定する事でリストが登録されます。
5.連動していない複数のプルダウンリストを作成
先ほどはリストの一覧から1列のみをリスト化しましたが、次は3列すべての絞り込みのない入力規則を作成する部分です。
なぜ必要かと言いますと、「何も選択されていない」状態では絞り込みされていないリストを表示する必要があるからです。
次のサンプルコードはいったん全ての入力規則を削除して、F2、F4、F6に絞り込みのない入力規則を設定します。
こちらはThisWorkBookモジュールに「Workbook_Open」イベントで下記サンプルコードのプロシージャを呼び出すことで、ファイルを開いたらリストが設定されます。
サンプルコード
ThisWorkBookモジュールの「Workbook_Open」イベント
ThisWorkBookモジュールに次のように記述します。
Private Sub Workbook_Open()
Call List1_Set
End Subリスト作成のサンプルコード
Sub List1_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
Dim CellInt As Long
With ActiveSheet
'既存の入力規則とデータを削除
.Range("F2,F4,F6").Validation.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
For n = 1 To 3 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
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を変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
CellInt = n * 2 '設定するセルの行数指定
'入力規則を設定
.Cells(CellInt, 6).Validation.Add Type:=xlValidateList, Formula1:=ListStr
Next n
End With
End Sub下記図では3つ目のリストのみ表示されていますが、3つのリストが作成されました。
コードの説明
Sample1と変わった部分のみ説明します。
「.Range(“F2,F4,F6”).Validation.Delete」で入力規則を作成する該当全てのセルの設定を削除します。
「For n = 1 To 3」で今回はメーカー、商品、サイズの3項目なので3列分ループします。
「Set CheckDic = CreateObject(“Scripting.Dictionary”)」を列のループに入れる事で毎回列ループ時にDictionaryを初期化します。
「CellInt = n * 2」は単純に設定するセルが今回はF2、4、6なので「1*2=2」、「2*2=4」、「2*3=6」が成り立ちましたので、セルを特定するために計算しただけです。
6.連動した複数のプルダウンリストの作成
次は連動したプルダウンリストの作成方法です。
今回の方法はメーカー→商品→サイズという絞り込みの依存関係の上に成り立つとします。
全項目に依存関係を持たせようとすると、出来ないこともありませんが非常に複雑になります。
Sample1の様にDictionaryのKeyを単純にリストに設定はできませんので、簡単な方法として各リストの設定を分けて作成する方法です。
ロジックはリストの1つ目が変更されると、2つ目、3つ目のリストを一旦リセットして、条件に一致するリストを再度作成して設定します。
2つ目のリストが変更されると3つ目のリストを一旦リセットして、1つ目と2つ目の条件に一致するリストを再度作成して設定します。
再設定は特定のセルが変更されたときのチェンジイベントで分岐します。
サンプルコード
Sheetモジュールの「Worksheet_Change」イベント
シートモジュールに次のように記述します。
F2のリストが削除された場合は、一旦すべてのリストを再設定する分岐も記述しています。
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row = 2 And .Column = 6 Then
If Range("F2") = "" Then
Call List1_Set
Else
Call List2_Set
End If
ElseIf .Row = 4 And .Column = 6 Then
If Range("F4") <> "" Then
Call List3_Set
End If
End If
End With
End Sub1つ目のリストを作成するコード
こちらは先ほどのコードと同じです。
標準モジュールに記述します。
Sub List1_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
Dim CellInt As Long
With ActiveSheet
'既存の入力規則とデータを削除
.Range("F2,F4,F6").Validation.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
For n = 1 To 3 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
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を変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
CellInt = n * 2 '設定するセルの行数指定
'入力規則を設定
.Cells(CellInt, 6).Validation.Add Type:=xlValidateList, Formula1:=ListStr
Next n
End With
End Sub2つ目のリストを作成するコード
1つ目のリストの条件に一致するリストの作成です。
標準モジュールに記述します。
Sub List2_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
Dim CellInt As Long
With ActiveSheet
'既存の入力規則を削除
.Range("F4,F6").Validation.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
For n = 2 To 3 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
For i = 3 To MaxRow '3行目から最終行までループ
If .Cells(2, 6) = .Cells(i, 1) Then '1つ目のリストに一致するデータのみ格納
Myval = Cells(i, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
End If
Next i
DicKey = CheckDic.keys 'Keyを変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
CellInt = n * 2 '設定するセルの行数指定
'入力規則を設定
.Cells(CellInt, 6).Validation.Add Type:=xlValidateList, Formula1:=ListStr
Next n
End With
End Sub1つ目を選択した状態で2つ目のリストが連動したリストに絞り込まれました。
3つ目のリストを作成するコード
1つ目と、2つ目のリストの条件に一致するリストを作成するコードです。
標準モジュールに記述します。
Sub List3_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
Dim CellInt As Long
With ActiveSheet
'既存の入力規則を削除
.Range("F6").Validation.Delete
'データを最終行取得する
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
For i = 3 To MaxRow '3行目から最終行までループ
If .Cells(2, 6) = .Cells(i, 1) And _
.Cells(4, 6) = .Cells(i, 2) Then '1つ目と2つ目のリストに一致するデータのみ格納
Myval = Cells(i, 3).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
End If
End If
Next i
DicKey = CheckDic.keys 'Keyを変数に格納
ListStr = Join(DicKey, ",") 'リストをカンマ区切りで統合して格納
'入力規則を設定
.Cells(6, 6).Validation.Add Type:=xlValidateList, Formula1:=ListStr
End With
End Sub同様に1つ目と2つ目の条件に一致したリストが作成されました。
