【Excel VBA】データの入力規則でドロップダウンリストを作成・活用する7つの方法

データの入力規則（データバリデーション）によるドロップダウンリストは、Excelでのさまざまな作業を効率化できる非常に便利な機能です。そして、Excel上であらゆる操作を実行するうえで、VBAを活用するのが最も効果的かつ迅速で安全な方法です。本記事では、VBAマクロを使ったデータの入力規則ドロップダウンリストの7つの活用シーンを、具体的なコードとともにわかりやすく解説します。

練習用ワークブックのダウンロード

本記事で使用する練習用のExcelワークブックは、こちらから無料でダウンロードできます。

VBAで実現するドロップダウンリストの7つの活用方法

このセクションでは、VBAマクロを使ってデータの入力規則ドロップダウンリストを操作する7つの方法を順番に学んでいきます。

1. VBAでデータの入力規則ドロップダウンリストを作成する

VBAでドロップダウンリストを作成する方法を知りたい方は、以下の手順に従ってください。

手順：

• まず、キーボードでAlt + F11を押すか、リボンの開発 → Visual Basicを選択して、Visual Basic Editor（VBE）を開きます。

• 次に、表示されたコードウィンドウのメニューバーから挿入 → 標準モジュールをクリックします。

• 続いて、以下のコードをコピーしてコードウィンドウに貼り付けます。

Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End Sub

これでコードを実行する準備が整いました。

このコードは、セルB5にドロップダウンリストを作成します。リストには「Grapes（ブドウ）、Orange（オレンジ）、Guava（グアバ）、Mango（マンゴー）、Apple（りんご）」という値が含まれます。

• キーボードのF5キーを押すか、メニューバーから実行 → Sub/ユーザーフォームの実行を選択してマクロを実行します。サブメニューバーの小さな実行アイコンをクリックしても構いません。

コード実行後の結果は、下の画像をご覧ください。

画像のとおり、セルB5に「Grapes、Orange、Guava、Mango、Apple」という値を持つドロップダウンリストが作成されました。

関連記事： Excelでデータの入力規則によるドロップダウンリストを作成する8つの方法

2. 名前定義（Named Range）を使ってドロップダウンリストを生成する

リストの値を一つひとつコード内に書きたくない場合は、すべての値を名前定義に登録しておき、その名前を呼び出すことで範囲を指定できます。これはExcelでドロップダウンリストを作成する際にとても便利な手法です。

ここでは、名前定義を使ってVBAコードからドロップダウンリストを生成する方法を解説します。

手順：

• まず、ドロップダウンリストの値が入力されている範囲を選択します（今回はB5:B9）。
• 次に、選択した範囲を右クリックします。
• 表示されたオプションの一覧から「名前の定義…」を選択します。

• すると「新しい名前」ダイアログボックスが表示されます。「名前」ボックスにお好みの名前を入力してください（ここではFruitsと命名しました）。
• 最後にOKをクリックします。

• これで、範囲B5:B9にFruitsという名前が正常に定義されました（下図参照）。

次に、この定義した名前をVBAコードで使用します。手順は以下のとおりです。

• 先ほどと同じように、開発タブからVisual Basic Editorを開き、コードウィンドウに標準モジュールを挿入します。
• そして、以下のコードをコピー＆ペーストします。

Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End Sub

これでコードを実行する準備ができました。

このコードは、名前Fruitsに定義された「Grapes、Orange、Guava、Mango、Apple」という値を持つドロップダウンリストをセルB12に作成します。

• その後、マクロを実行します。結果は下の画像のとおりです。

画像のとおり、セルB12に「Grapes、Orange、Guava、Mango、Apple」という値を持つドロップダウンリストが作成されました。

関連記事： Excelで複数選択可能なデータの入力規則ドロップダウンリストを作成する方法

3. マクロで既存のリスト範囲からドロップダウンボックスを作成する

名前定義を使いたくない方には、こちらの方法がおすすめです。ここでは、ワークシート上に存在する範囲を直接参照してドロップダウンリストを作成する方法を学びます。

手順：

• 前述の手順と同様に、開発タブからVisual Basic Editorを開き、コードウィンドウに標準モジュールを挿入します。
• 次に、以下のコードをコピーしてコードウィンドウに貼り付けます。

Sub ProduceDropDownList()
With Range("B12").Validation
 .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
 .InCellDropdown = True
End With
End Sub

これでコードを実行する準備が整いました。

このコードは、範囲B5:B9に入力されている値を使って、セルB12にドロップダウンリストを作成します。

• マクロを実行し、下の画像で出力結果を確認しましょう。

画像のとおり、ワークシートのセルB5～B9に保存しておいた「Grapes、Orange、Guava、Mango、Apple」という値を持つドロップダウンリストがセルB12に作成されました。

関連コンテンツ： Excel VBAでデータの入力規則リストに初期値を設定する方法（マクロとユーザーフォーム）

あわせて読みたい記事：

• フィルター機能付きのExcelデータ入力規則ドロップダウンリスト（2つの方法）
• カスタム数式を使った英数字のみの入力規則
• 別のセルの値に基づくExcelデータの入力規則
• Excelのデータ入力規則でカスタムVLOOKUP数式を使う方法

4. VBAで複数のドロップダウンリストを一括作成する

VBAマクロを使えば、複数のドロップダウンリストを一度に作成することも可能です。具体的な手順を見ていきましょう。

手順：

• まず、開発タブからVisual Basic Editorを開き、コードウィンドウに標準モジュールを挿入します。
• 次に、以下のコードをコピー＆ペーストします。

Sub MultipleDropDownList(iTarget As Range, iSource As Range)
    '対象範囲の入力規則を削除して再設定する
    With iTarget.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & iSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Sub DropDownRange()
    MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3")
End Sub

これでコードを実行する準備ができました。

このコードは、B5からB9までの範囲内のすべてのセルにドロップダウンリストを生成します。

• マクロを実行し、下のGIF画像で結果を確認してください。

B5からB9までのすべてのセルに、それぞれドロップダウンリストが設定されました。

関連記事： Excelで複数条件に対応するカスタムデータの入力規則を適用する方法（4つの例）

5. ユーザー定義関数（UDF）でドロップダウンリストを作成する

Excelでは、ユーザー定義関数（UDF）を使ってドロップダウンリストを作成することもできます。

手順は以下のとおりです。

手順：

• まず、UDFを実装したいシートタブを右クリックします。
• 表示された一覧から「コードの表示」を選択します。下図では、データセットが保存されているUDFという名前のシートを右クリックし、オプションから「コードの表示」を選択しています。

• 次に、自動生成されたコードウィンドウに、以下のコードをコピー＆ペーストします。

Public Function DropDownUDF(iSource As Range) As Variant
    '指定範囲の入力規則を削除して再設定する
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & iSource.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    '最初の値を返す
    'シートの数式が更新されたときに値をリセットする
    DropDownUDF = VBA.Val(iSource(1))
End Function

• このコードは実行しないでください。保存するだけです。

• その後、対象のワークシートに戻ります。
• 続いて、ドロップダウンリストを作成したい任意のセルを選択します（今回はセルB11）。
• そのセルに、通常の関数と同じ要領で新しく作成した関数DropDownUDFを入力します。つまり、最初に等号（=）を入力し、次に関数名DropDownUDFを書き、括弧の中にセル参照（B5:B9）を渡します。

セルB11に入力する数式は次のようになります。

=DropDownUDF(B5:B9)

• そして、Enterキーを押します。

その結果、関数に渡した範囲B5:B9に保存されている「Grapes、Orange、Guava、Mango、Apple」という値を持つ、UDFによって作成されたドロップダウンリストがセルB11に表示されます。

関連記事： Excelのデータ入力規則の数式でIF文を使う方法（6通り）

6. 別シートのデータをドロップダウンリストとして抽出する

下の画像をご覧ください。「List」という名前のシートにデータセットがあります。

ここで行うのは、「Target」シート（下図参照）のセルB5にドロップダウンリストを作成することです。そして、そのドロップダウンリストの値には、Listシートの範囲B5:B9の値を使用します。

VBAでこれを実現する手順を見ていきましょう。

手順：

• まず、開発タブからVisual Basic Editorを開き、コードウィンドウに標準モジュールを挿入します。
• 次に、以下のコードをコピー＆ペーストします。

Private Sub DropDownFromSheet()
'セルB5にドロップダウンリストを格納する
'"B5"は他のセルに置き換えても構いません
With Range("B5").Validation
.Delete
'"List"シートの"B5:B9"範囲からデータを抽出する
'"=List!B5:B9"は実際のシート名と範囲に置き換えてください
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=List!B5:B9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

これでコードを実行する準備が整いました。

• その後、マクロを実行し、下の画像で出力結果を確認してください。

コードが正常に実行されると、「List」シートの範囲B5:B9に保存されている「Grapes、Orange、Guava、Mango、Apple」という値を持つドロップダウンリストが、「Target」ワークシートのセルB5に作成されます。

関連記事： 別のシートからデータの入力規則リストを使う方法（6つの方法）

7. VBAマクロでドロップダウンリストを削除する

このセクションでは、Excelからドロップダウンリストを削除する方法を解説します。セルB5に存在する既存のドロップダウンリスト（下図参照）を、VBAマクロで削除してみましょう。

実行手順は以下のとおりです。

手順：

• まず、開発タブからVisual Basic Editorを開き、コードウィンドウに標準モジュールを挿入します。
• 次に、コードウィンドウに以下のコードをコピー＆ペーストします。

Sub DeleteDropDownList()
Range("B5").Validation.Delete
End Sub

これでコードを実行する準備が整いました。

• マクロを実行し、下の画像をご確認ください。

画像のとおり、セルB5からはドロップダウンリストが消えています。これで、VBAを使ってスプレッドシートから既存のドロップダウンリストを削除する方法も習得できました。

関連記事： Excelのデータ入力規則リストから空白を除去する方法（5つの方法）

まとめ

本記事では、VBAマクロを使ったデータの入力規則ドロップダウンリストの7つの活用方法を紹介しました。リストの新規作成から名前定義の利用、別シート参照、複数セルへの一括設定、そして削除まで、実務ですぐに使えるテクニックばかりです。この記事が皆さまのお役に立てば幸いです。本テーマに関するご質問があれば、お気軽にお問い合わせください。

関連記事

• 【解決済み】Excelでコピー＆ペースト時にデータの入力規則が機能しない問題の対処法
• Excel VBAで名前定義を使ったデータの入力規則リストを活用する方法
• Excel VBAで配列からデータの入力規則リストを作成する方法
• Excelで色を使ったデータの入力規則を適用する方法（4つの方法）
• Excelの1つのセルに複数のデータの入力規則を適用する方法（3つの例）