【エクセルVBA】入力規則のドロップダウンリストを複数連動させる方法

皆様こんにちは、ノグチです。

前回の記事では、エクセルの入力規則のドロップダウンリストにセットする文字列の文字数制限と、どんな方法で文字列をセットするとこの文字数制限にひっかかるのか？を検証してご紹介しました。

【エクセルVBA】255文字を超えたらどうなるの？入力規則のリスト設定方法あれこれ

エクセルの入力規則にあるリストで作成できるドロップダウンリスト。ドロップダウンリストに表示したい文字列は255文字の制限がありますが、255文字を超えて文字列をドロップダウンリストに指定するとどうなるのか、この文字制限はどの指定方法に適用されるのか？を検証しています。

tonari-it.com

2019.02.15

そして今回もまた、この入力規則のドロップダウンリストについてご紹介していきます。

ドロップダウンAから文字列を選択したら、選択した文字列に関連する文字列だけドロップダウンBに表示させたいな～となんてこと、ありませんか？

例えば通販サイトで、欲しいボールペンを選択したら、そのボールペンで販売されている色だけがドロップダウンリストに表示される…というイメージです。

エクセルでも、見積書や請求書を作成するツールにあると便利ですよね。

それ、VBAなら作れるかもしれませんよ。

ということで今回は、VBAで複数のドロップダウンリストを連動して使う方法をご紹介します。

ドロップダウンリストを連動させる為の4ステップ

では早速、こちらのワークシートのB2セルにあるプルダウンリストから文字列を選択したら、選択した文字列をG列に持つH列の文字列だけをC2列のドロップダウンリストに表示させられるようにしてみましょう。

例えば、B2セルのドロップダウンリストから「いちご」を選択したら、G列に「いちご」を持つH列の「佐賀」「福岡」「栃木」という文字列をC2セルのドロップダウンリストに表示させる、という場合です。

文字で見てみるとちょっと難しそうに思えてしまうかもしれませんが、やることをステップに分けて考えればそこまで難しくありません。

下記4ステップでコードを作ってみましょう。

1. Worksheet.Changeイベントで、B2セルのドロップダウンリストが選択されたことをキャッチ
2. B2セルで選択された文字列とマッチする値を持っているセルをG列から検索してG列と同じ行のH列の値をJ列に書き出し
3. J列の検索結果を書き出した範囲を、名前付き範囲として定義
4. C2セルのドロップダウンリストの値に、J列の名前付き範囲をセット

Worksheet.Changeイベントでドロップダウンリストの変更をキャッチする

さて、B2セルのドロップダウンから値を選択したら、C2セルのドロップダウンリストに関連文字列だけ表示したい…ということは、B2セルのドロップダウンリストが変更されることが、C2セルのドロップダウンリストの文字列を変更させるトリガーになるわけですね。

ならば、Worksheet.changeイベントで、ドロップダウンリストAがあるセルの値が変わったか？を見てあげればよさそうです。

イベントにコードを差し込む方法や、注意点についてはこちらの記事で紹介していますので、併せてご覧下さい。

【エクセルVBA】イベントを使ってもっと便利なツールにしてみよう！WorksheetオブジェクトのChangeイベント

エクセルのイベント、WorksheetオブジェクトのChangeイベントをご紹介しています。シートに対して、セルの入力や削除などがあった時の処理をこのイベントに仕込んでおけば、変更のアクション終了と同時に処理が実行されて便利ですよ！

tonari-it.com

2018.06.04

Worksheet.changeイベントは、引数Targetで変更があったセルをRange型で渡してくれるのでしたね。

なので、こんな感じでB2セルの変更をキャッチしましょう。

Private Sub Worksheet_Change(ByVal Target As Range)

With ActiveSheet
    If Target = .Range("B2") Then   'ドロップダウンリストの文字列を選択したら 
    End If
End With

End Sub

これでB2セルのドロップダウンリストが変更されたことを、VBAでキャッチできるようになりました。

選択した文字列から検索した結果を書き出す

お次はB2セルで選択した文字列をG列に持つセルと同じ行のH列にある文字列を検索しましょう。

これは、FindとFindNextメソッドを使えば簡単。

FindメソッドとFindNextメソッドの使い方は、下記の記事をご覧くださいね。

【エクセルVBA】FindNextメソッドで指定範囲内の検索条件にヒットする値をすべて取得する方法

Findextメソッドを使って、指定した範囲内で検索条件にヒットする値を全て取得する方法をご紹介しています。Findメソッドだけだと、指定範囲で一番最初に検索にヒットする値しか取得できませんが、FindNextメソッドなら複数の検索結果を取得できますよ。

tonari-it.com

2018.07.06

上のコードと組み合わせて、こんな風に記述できます。

Dim myRange As Range
Dim rngSearch As Range
Dim i As Long
Dim strAdr As String

Set myRange = .Range("G2:G9")   'G列の検索範囲をセット
Set rngSearch = myRange.Find(What:=.Range("B2"), LookAt:=xlPart)    'G列を検索
        
Application.EnableEvents = False

If Not rngSearch Is Nothing Then
    i = 2

    'ヒットした値をJ列に格納
    .Cells(i, 10).Value = .Cells(rngSearch.Row, 8).Value

    'ヒットした値のセルを退避
    strAdr = rngSearch.Address

    Do
        Set rngSearch = myRange.FindNext(rngSearch)
        If rngSearch Is Nothing Then
            Exit Do
        Else
            If strAdr <> rngSearch.Address Then
                i = i + 1
                .Cells(i, 10).Value = .Cells(rngSearch.Row, 8).Value
            End If
        End If
    Loop While rngSearch.Address <> strAdr

End If

検索して見つかった文字列を文字列型の変数に格納して、それをドロップダウンリストの値としてセットする方法もありますが、前回記事でご紹介した通り、ドロップダウンリストにセットする文字数が255文字を超えると実行時エラーになってしまいます。

なのでこの記事では、ドロップダウンリストにセットする文字列は名前付き範囲としてドロップダウンリストの値にセットしたいので、一旦J列に並べるようにしています。

検索結果を名前付き範囲として定義する

J列に書き出した検索結果を、名前付き範囲として定義してしまいましょう。

こんな感じ。

Dim rngResult   As String

rngResult = "Sheet1!" & "$J$2:$J$" & i
ActiveSheet.Names.Add Name:="検索結果", RefersTo:="=" & rngResult

名前付き範囲をドロップダウンリストの値にセット

最後に、名前付き範囲として定義した検索結果のセル範囲を、ドロップダウンリストにセットしましょう。

ドロップダウンリストに値をセットするためには、ValidationオブジェクトのAddメソッドを使うのでしたね。

なので、上のコードにこのメソッドを差し込みます。

With .Range("C2").Validation
     .Delete
     .Add Type:=xlValidateList, _
     AlertStyle:=xlValidAlertStop, _
     Formula1:＝"=検索結果"
End With

複数のドロップダウンを連動させるコード

この3ステップのコードを組み合わせると、こんなコードになります。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim rngSearch As Range
Dim i As Long
Dim strAdr As String
Dim rngResult As String

With ActiveSheet

    If Target = .Range("B2") Then   'ドロップダウンリストの文字列を選択したら
        Set myRange = .Range("G2:G9")   'G列の検索範囲をセット

        Set rngSearch = myRange.Find(What:=.Range("B2"), LookAt:=xlPart)    'G列を検索
        
        Application.EnableEvents = False

        If Not rngSearch Is Nothing Then
            i = 2

            'ヒットした値をJ列に格納
            .Cells(i, 10).Value = .Cells(rngSearch.Row, 8).Value

            'ヒットした値のセルを退避
            strAdr = rngSearch.Address

            Do
                Set rngSearch = myRange.FindNext(rngSearch)
                If rngSearch Is Nothing Then
                    Exit Do
                Else
                     If strAdr <> rngSearch.Address Then
                        i = i + 1
                        .Cells(i, 10).Value = .Cells(rngSearch.Row, 8).Value
                    End If
                End If

            Loop While rngSearch.Address <> strAdr

            '名前付き範囲の範囲更新
            rngResult = "Sheet1!" & "$J$2:$J$" & i
            
            ActiveSheet.Names.Add Name:="検索結果", RefersTo:="=" & rngResult

        End If

        With .Range("C2").Validation
            .Delete
            .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:="=検索結果"
        End With

        Application.EnableEvents = True

        .Range("C2").Select

    End If

End With

End Sub

この通り、B2セルのドロップダウンリストで選択した値に応じて、C2セルのドロップダウンリストに検索結果がセットされていますね。

最後に

今回は、VBAで複数のドロップダウンリストを連動させる方法をご紹介しました。

ドロップダウンリストは、何かと便利なものですよね。

複数のドロップダウンリストを組み合わせて使うことができれば、更に便利に使えそうです。

それでは、最後までお読みいただきありがとうございました！