【Excel VBA入門】ドロップダウンリストの作り方。複数リストの連動方法も解説

こんにちは、業務自動化ツール開発担当の吉池（@valmore_myoshi）です。

今回はExcel VBAでドロップダウンリストを作る方法を解説します。入力の手間を減らしたいときはもちろん、選択できる値が決められているので予期せぬ値の入力も防げます。

一つの商品を選んだら、その商品のサイズを選ぶなど、複数のドロップダウンを連動させることもあるはずです。本記事ではドロップダウンリスト同士を連動させる方法も解説しているので、ぜひ参考にしてください。

ドロップダウンリストの作り方

ドロップダウンリストを作るには入力規則を操作するValidationオブジェクトのAddメソッドを使います。Validationオブジェクトは特定の範囲を表すRangeオブジェクトのValidationプロパティから取得できます。

Range("B2").Validation. _
     Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="りんご,バナナ,みかん"

Addメソッドに指定する引数は複数ありますが、ドロップダウンリストを作るなら以下の３つで十分です。

引数	オプション	説明
Type	必須	入力規則の種類。ドロップダウンリストの場合はxlValidateListを指定。他にもデータの種類や文字数制限を選択可。
AlertStyle	任意	入力規則から外れた値を入力したときに表示するアラートの種類を選択。xlValidAlertInformation, xlValidAlertWarning, xlValidAlertStopの３種類。
Formula1	任意	入力規則のルールを設定。ドロップダウンリストの場合は必須。カンマ区切りで選択肢を列挙。255文字まで。

AlertStyleは入力規則から外れた値を許容するか否かでアラートの種類を決めてください。xlValidAlertInformationはドロップダウンリストにない値でも入力できますが、xlValidAlertStopは受け付けません。xlValidAlertWarningは警告を表示します。

配列を選択肢にするときはTranspose関数を使う

ドロップダウンリストの選択肢はカンマ区切りの文字列で指定しなければなりません。一方、選択肢は特定のセル範囲から持ってきたいというのが一般的でしょう。つまり、セル範囲を文字列に置き換える手間がどうしても生じてしまいます。

しかし、ここでセル範囲を配列として取得すると、Join関数を使ってカンマ区切り文字列へ簡単に置き換えられます。例えば、下記のような商品一覧のドロップダウンリストを配列を使って作ってみましょう。

選択肢となるセル範囲をRangeオブジェクトで指定し、単純にValueプロパティを変数に代入すると二次元配列になってしまいます。このままではJoin関数が使えないので、Transposeワークシート関数を使って一次元配列に置き換えます。

Dim items() As Variant

items = WorksheetFunction.Transpose(Range("B5:B9").Value)

Range("B2").Validation. _
     Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(items, ",")

１列のセル範囲の場合は今回のようにTransposeワークシート関数を一度使って一次元配列に変換できますが、１行のセル範囲の場合は二回使う必要があるので注意してください。

Dim items() As Variant

With WorksheetFunction
    items = .Transpose(.Transpose(Range("C4:G4").Value))
End With

Range("B2").Validation. _
     Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(items, ",")

既存の入力規則は削除する

ドロップダウンリストを追加しようとしたセルに入力規則が設定してあると下記のようなエラーになります。

すでに入力規則が設定してあると想定して、はじめに入力規則を削除するDeleteメソッドを入れることでエラーを防げます。

Dim items() As Variant

items = WorksheetFunction.Transpose(Range("B5:B9").Value)

With Range("B2").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(items, ",")
End With

ドロップダウンリスト同士を連動させる方法

二つ以上のドロップダウンリストを連動させたいという要望もあるあるです。一つ目のドロップダウンリストで商品を選んだ瞬間、その商品の色やサイズを二つ目のドロップダウンに連動させるという感じです。

この連動の仕組みを作るロジックは、一つ目のドロップダウンリストの選択が完了したというイベントを検知し、二つ目のドロップダウンに選択肢を格納すると考えます。

それでは、以下の商品表を例にドロップダウンリスト同士を連動させてみましょう。

まずは、一つ目のドロップダウンリストを作りましょう。商品名の重複を削除した新たな配列からドロップダウンリストを作っています。

Dim items() As Variant
Dim i As Integer
Dim tmp As String
Dim itemsStr As String

tmp = ""
itemsStr = ""

' 商品名取得
items = WorksheetFunction.Transpose(Range("B6:B13").Value)

' 重複しない商品名をカンマ区切り文字列に変換
For i = 1 To UBound(items)
    If tmp <> items(i) Then
        itemsStr = itemsStr & items(i) & ","
        tmp = items(i)
    End If
Next

' ドロップダウンリスト作成
With Range("B3").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Left(itemsStr, Len(itemsStr) - 1)
End With

Changeイベントで値の変化を検知

続いて一つ目のドロップダウンリストの選択（正確にはセルの値の変化）を検知して、二つ目のドロップダウンリストに値を格納する関数を作りましょう。この関数はシートごとに用意されているモジュールに書いていきます。

①モジュールを選択して、コードウィンドウから②Worksheet、③Changeを選択すると自動的に関数が作られます。

Private Sub Worksheet_Change(ByVal Target As Range)
    ‘ 引数Targetにはセルの値が変化したRangeオブジェクトが格納される
End Sub

一つ目のドロップダウンリストの値が変わったときに処理を実行するので、If文でチェックします。そして、特定の値を繰り返し検索するFindNextメソッドを使って商品名に紐づくサイズを取得し、二つ目のドロップダウンリストに格納しています。

Dim item As Range
Dim itemsStr As String
Dim firstAddress As String
Dim filterdItems As Variant

' 一つ目のドロップダウンリストの変化を検知
If Target = Range("B3") Then
    Set item = Range("B6:B13").Find(Target.Value)
    
    If Not item Is Nothing Then
        itemsStr = ""
        firstAddress = item.Address
        
        ' 商品名を繰り返し検索し、見つかったらサイズをカンマ区切り文字列で取得
        Do
            itemsStr = itemsStr & item.Offset(0, 1).Value & ","
            
            Set item = Range("B6:B13").FindNext(item)
        Loop While item.Address <> firstAddress
        
        ' 二つ目のドロップダウンリストにサイズを格納
        With Range("C3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Left(itemsStr, Len(itemsStr) - 1)
        End With
    End If
End If

まとめ

ドロップダウンリストの作り方を解説しました。入力の手間を減らすときや意図した値を選択させるときにドロップダウンは役立ちます。

VBAでドロップダウンを作るとなると、選択肢を動的に変えたいときではないでしょうか？そんなときは選択肢を取得するタイミング、つまりイベントが必須になります。本記事で解説したChangeイベントをはじめとして、使い方をマスターしましょう！