Excel VBA 入力規則に3つ以上の連動したプルダウンリストを設定する方法

ExcelVBA-実用編

今回はExcel機能の入力規則で、連動した複数のプルダウンリストを設定する方法をご説明します。

Excelで入力規則を操作しても、データ構造等をしっかり組み立てる事で、2つまでの連動であれば比較的簡単に設定する事は出来ますが、3つ以上となると非常に複雑で難しくなります。

VBAを使うとこの複雑な設定が簡単にできます。

この様なケースのツールはシンプルかつコンパクトに作成すると、とても難しくなりますので、今回は簡単な方法で実装したいと思います。

VBAで入力規則を操作する方法と、データ構成も併せてご説明します。

Excelで入力規則を使用してプルダウンリストを作成する方法は「入力規則の基本説明と動的にプルダウンリストを作成する方法」をご覧ください。

1.入力規則のリスト用のデータ構成

VBAで入力規則の連動したリストを作成するには難しい構成は必要ありません。

それぞれの項目の階層が紐づくようにデータの一覧を作成します。

重複したデータが存在しても構いませんが管理が煩雑になりますので、お勧めしません。

また、極力各階層順に並び替えしておくことでリストの並びが整います。

2.入力規則の削除方法

セルに入力規則を設定する方法を説明する前に、入力規則の削除方法について説明します。

理由は、すでにセルに入力規則が設定されている場合はエラーとなります。

ですので、基本的に入力規則を設定する前に既存の入力規則を削除します。

削除するには設定されているセル(オブジェクト)の「Validationオブジェクト」を操作します。

記述方法は簡単で次のように記述します。

Range("設定されている範囲").Validation.Delete

3.入力規則のValidationオブジェクトと引数の説明

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

そして引数でそれぞれ詳細を設定します。

次のように記述します。

Validation.Add Type,AlertStyle,Operator,Formula1,Formula2

Validationの引数一覧

引数名省略説明
Type必須入力値の種類を指定します。
AlertStyle省略可能エラー時の動作を指定します。
Operator省略可能指定された値を比較する方法を指定します。
Formula1省略可能リストに指定したい値を指定します。
Formula2省略可能引数「Operator」で「xlBetween」もしくは「xlNotBetween」 を
指定した場合に、入力できる範囲の最大もしくは最小を指定します。
Typeの定数一覧

「Type」は 入力規則の種類を指定します。

Excelでは次の画面の操作に該当します。

定数数値説明
xlInputOnly0すべての値
xlValidateWholeNumber1整数
xlValidateDecimal2小数点
xlValidateList3リスト指定
xlValidateDate4日付指定
xlValidateTime5日時指定
xlValidateTextLength6文字列(長さ指定)
xlValidateCustom7ユーザ設定
AlertStyleの定数一覧

「AlertStyle」はエラー時の動作を指定します。

Excelでは次の画面の操作に該当します。

定数数値説明
xlValidAlertStop1停止
xlValidAlertWarning2注意
xlValidAlertInformation3情報
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 Sub
1つ目のリストを作成するコード

こちらは先ほどのコードと同じです。

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
2つ目のリストを作成するコード

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 Sub

1つ目を選択した状態で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つ目の条件に一致したリストが作成されました。

タイトルとURLをコピーしました