別シートにリストを作成し、VBAで連動するプルダウンを作成する：Excel Tips

連動するプルダウンとは、複数のプルダウンを使用し、１つ目に選んだデータに応じたリストを次のプルダウンに表示させることをいいます。

このページでは、１つ目のプルダウンから北海道・東北・九州のどれかを選択すると、２つ目のプルダウンでその地域内の地名のリストから選択できるようにします。

別シートにプルダウンで使用するリストを作成する

Sheet2に下のリストを作成しました。

１．項目名の北海道・東北・九州のセルを選択します。
２．セルに名前を付けます。　名前を付けるには、名前ボックスに名前を入力します。 ここでは「地域」にしました。


１．リスト全体を選択します。
２．リボンの［数式］～定義された名前グループの［選択範囲から作成］をクリックします。


選択範囲から名前を作成ダイアログが開きます。
［上端行］をチェックし［OK］ボタンをクリックします。


これでプルダウンで使用するリストが完成しました。

VBAで連動するプルダウンを作成

ワークブックが開いた時に発生するイベント「Workbook_Open」に入力します。


・B2セルに１つ目のブルダウンを作成し、上で付けた名前の「地域」をリストに設定します。
・C2セルに２つ目のブルダウンを作成し、B2が空白でなければINDIRECT関数でB2を参照するようにします。

Private Sub Workbook_Open()
    
    With Sheet1.Range("B2").Validation
         .Delete
         .Add Type:=xlValidateList, Formula1:="=地域"
    End With
     
    With Sheet1.Range("C2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=IF(B2="""",B2,INDIRECT(B2))"
    End With

End Sub

これでVBAが出来たので、Workbook_Openを実行させるために、ブックを閉じて再度開きます。

１つ目のプルダウンで地域を選択します。


北海道を選択すると、２つ目のプルダウンは北海道の地名から選択できます。


東北を選択すると、２つ目のプルダウンは東北の地名から選択できます。


九州を選択すると、２つ目のプルダウンは九州の地名から選択できます。


空白にすると、２つ目のプルダウンのリストは空になります。