Excel VBAで複数の連動型ドロップダウンリストを作成する3つの方法

MS Excelで複数選択可能な連動型ドロップダウンリストを作成するのは、常に難しい課題とされてきました。2つのドロップダウンリスト間に依存関係や関連性がある場合、このようなリストが必要になります。通常はさまざまな数式やExcelのオプション設定を使って作成しますが、Excel VBAのコードを使えば、より簡単に複数のドロップダウンリストを作成できます。本記事では、VBAコードを使用して複数のドロップダウンリストを作成するさまざまな方法をご紹介します。

関連記事: Excelでドロップダウンリストを作成する方法(独立型・連動型)

Excelにおける連動型ドロップダウンリストとは?

本題に入る前に、Excelにおける連動型ドロップダウンリストとは何かを確認しておきましょう。2つ以上のドロップダウンリスト間に依存関係がある場合、それらを「連動型ドロップダウンリスト」と呼びます。以下の画像は、連動型ドロップダウンリストの概念をわかりやすく示したものです。

ご覧のとおり、「カテゴリー」と「食品」の2つのドロップダウンリストが完全に連動しています。カテゴリーの選択内容に応じて、食品のリストが定義されます。これが、複数のカスケード型(階層型)ドロップダウンリストの基本的な仕組みです。

関連記事: Excelで動的な連動型ドロップダウンリストを作成する方法

VBAで複数の連動型ドロップダウンリストを作成する3つの方法

方法1: ドロップダウンリストで複数選択を可能にする

ここでは、「プロジェクト名」と「プロジェクトメンバー」という2つのリストがあると仮定します。各プロジェクトに対して、ドロップダウンリストを使って1人または複数のメンバーを割り当てていきます。

ステップ1: 「開発」タブに移動し、「Visual Basic」を開きます(ショートカットキー: Alt + F11)。

ステップ2: VBAProjectメニューから、対象となるワークシートを選択します。

ステップ3: VBAエディタに以下のコードを入力します。

コード:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Old_value As String
 Dim New_value As String
 Application.EnableEvents = True
 On Error GoTo Exitsub
 If Not Intersect(Target, Range("C4:C11")) Is Nothing Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
      GoTo Exitsub
   Else: If Target.Value = "" Then GoTo Exitsub Else
      Application.EnableEvents = False
      New_value = Target.Value
      Application.Undo
      Old_value = Target.Value
         If Old_value = "" Then
            Target.Value = New_value
         Else
            If InStr(1, Old_value, New_value) = 0 Then
               Target.Value = Old_value & ", " & New_value
         Else:
            Target.Value = Old_value
         End If
      End If
   End If
 End If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
End Sub

ステップ4: 「プロジェクトメンバー」列で複数の名前を選択してみましょう。

ステップ5: すべてのセルで、ドロップダウンリストから複数選択が可能になります。

関連記事: Excelのドロップダウンリストで複数選択を行う方法

方法2: 複数の連動型ドロップダウンリストを作成する

ここでは、野菜、果物、乳製品といったカテゴリー別の食品データセットを用意します。カテゴリーに応じて食品項目を検索できるようにしたいと考えます。たとえば、カテゴリーとして「果物」を選択すると、「食品」列にはラズベリー、アプリコット、桃、マンゴーなどの該当項目が表示されるようにします。つまり、食品項目はカテゴリーに応じて表示される必要があり、「カテゴリー」と「食品」の間には依存関係が存在するということです。

ステップ1: 方法1と同じ手順(ステップ1およびステップ2)でVBAエディタを開き、以下のコードを入力します。

コード:

野菜のドロップダウンリストを作成する場合:

Sub Vegetable_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Vegetable_List"
 
End Sub

果物のドロップダウンリストを作成する場合:

Sub Fruit_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Fruits_list"
 
End Sub

乳製品のドロップダウンリストを作成する場合:

Sub Dairy_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Dairy_Product_List"
 
End Sub

この部分では、個々の食品項目のリストを作成し、それぞれをドロップダウンリストとして保存しています。これらのリストはC4:C6の範囲で利用可能になります。

ステップ2: 次に、B4:B6の範囲用のメイン関数を記述します。

コード:

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("B4:B6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  Formula1:="Vegetable_List,Fruits_list,Dairy_Product_List"
  If Range("B4:B6").Value = "Vegetable_List" Then
   Call Vegetable_List
  ElseIf Range("B4:B6").Value = "Fruits_list" Then
  Call Fruit_List
  ElseIf Range("B4:B6").Value = "Dairy_Product_List" Then
  Call Dairy_List
  Else
  End If

コードの解説

• まず、B4:B6の範囲にカテゴリー用のもう1つのリストを作成します。このリストには食品カテゴリーの名前が含まれます。
• 次に、リストの値をチェックし、対応する項目に応じて分類します。この処理にはIF ELSEステートメントを使用します。
• 一致する名前が見つかった場合は、CallBackメソッドを使ってリスト作成関数を呼び出します。たとえば以下のようになります。

If Range("B4:B6").Value = "Vegetable_List" Then

Call Vegetable_List

• ここで、セルの値がVegetable_Listというテキストと一致した場合、Vegetable_List関数を呼び出して野菜リストを作成・表示します。

以上をまとめると、完全なコードは次のようになります。

ステップ3: ワークシートに戻り、ドロップダウンリストから任意のカテゴリーを選択します。

ステップ4: すると、「食品」列に関連する項目が表示されます。

ステップ5: 最終的な結果は以下のようになります。

方法3: 連動型ドロップダウンリストの値をクリアする

前のセクションでは、一致する関連リストを取得する方法のみをご紹介しました。しかし、カテゴリーを変更しても、以前の選択内容が自動的に消えず、不整合が残ってしまうことがあります。このような問題を防ぐには、数式を利用する方法があります。

もう1つの有効な手段は、マクロを使って、最初のドロップダウンで選択を変更した際に依存先のセルを自動的にクリアすることです。これにより、不整合のある選択を防ぐことができます。

ステップ1: 方法1と同じ手順(ステップ1およびステップ2)でVBAエディタを開き、以下のコードを入力します。

コード:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

ステップ2: まず「食品」列から任意の項目を選択し、その後「カテゴリー」で別のカテゴリーを選択し直して、何が起こるか確認してみましょう。

変更前:

変更後:

最終結果:

関連記事: Excelでドロップダウンリストを削除する方法

注意点

よくあるエラー	発生する状況
リストを削除できない	データの入力規則で「入力値の種類」が「リスト」以外になっていたり、元の値が正しく選択されていなかったりすると、ドロップダウンリストを削除できません。その場合はVBAコードを使ってリストを削除する必要があります。
値が更新されない問題	一般的に、連動型ドロップダウンリストでは、不整合の値が存在すると自動的に更新されません。数式またはVBAコード(本記事の方法3)を使うことで、値を自動的に更新できます。

まとめ

本記事では、Excel VBAを使って複数の連動型ドロップダウンリストを作成・操作するいくつかの方法をご紹介しました。すべての方法を具体例とともに解説しましたが、その他にもさまざまな応用が可能です。使用した関数の基本についても触れました。もし他に実現方法をご存じでしたら、ぜひ共有していただければ幸いです。

さらに学ぶための関連記事

• Excelで複数列にドロップダウンリストを作成する方法(3つの方法)
• 選択内容に応じて変化するExcelのドロップダウンリスト
• IF関数を使ってExcelにドロップダウンリストを作成する方法
• 別シートのデータからExcelにドロップダウンリストを作成する(2つの方法)
• Excelでドロップダウンリストを編集する方法(4つの基本アプローチ)
• ExcelでドロップダウンリストとVLOOKUPを組み合わせる方法