Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

Excel VBAでドロップダウンリストから値を選択する2つの方法

ドロップダウンリストは、Excelでさまざまな作業を効率化できる非常に便利な機能です。Excelでの操作を実行する方法として、VBAの活用は最も効果的かつ迅速で安全な手段といえます。本記事では、VBAマクロを使ってドロップダウンリストから値を選択する方法を、2つの有効なアプローチで詳しく解説します。

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

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

汎用リストからドロップダウンリストを作成する方法

コードの解説に入る前に、まずはExcelの汎用リストからドロップダウンリストを作成する簡単な方法を学びましょう。後ほど、ここで作成したドロップダウンリストを記事のサンプルとして使用します。

以下の画像は、Excelワークシート上にある汎用リストです。このリストには重複した値(例:セルB7とB9の「りんご」)が含まれています。

Excel VBAでドロップダウンリストから値を選択する2つの方法

次に、この汎用リストから値(ぶどう・オレンジ・りんご・マンゴー・りんごなど)を含むドロップダウンリストを作成する手順を見ていきましょう。

手順:

  • まず、ドロップダウンリストを配置したいセル(今回はセルD4)をクリックします。
  • 次に、リボンの「データ」タブをクリックします。
  • 続いて、「データツール」グループにある「データの入力規則」を選択します。

Excel VBAでドロップダウンリストから値を選択する2つの方法

  • 「データの入力規則」ダイアログボックスが表示されます。そこで以下を設定します。
    • 「入力値の種類」で「リスト」を選択します。
    • 「元の値」欄に、ドロップダウンリストに表示したい値が入力された範囲(今回はB5:B9)をドラッグして指定します。
  • 最後に「OK」をクリックします。

Excel VBAでドロップダウンリストから値を選択する2つの方法

以下の画像をご覧ください。

Excel VBAでドロップダウンリストから値を選択する2つの方法

セルD4には、汎用リスト(範囲B5:B9)から取得した値(ぶどう・オレンジ・りんご・マンゴー・りんごなど)を保持するドロップダウンリストが作成されました。

VBAを使ってドロップダウンリストから複数の値を選択する2つの方法

このセクションでは、VBAを使用してドロップダウンリストから重複あり・重複なしの両方のパターンで複数の値を選択する方法を学びます。

方法1:重複した値も選択できるようにするVBAコード

データセットには重複した値が含まれています。値が重複していてもすべて選択対象にしたい場合は、以下の手順に従ってください。

手順:

  • まず、キーボードで「Alt + F11」を押すか、「開発」タブ→「Visual Basic」を選択して、Visual Basic Editor(VBE)を開きます。

Excel VBAでドロップダウンリストから値を選択する2つの方法

  • 次に、対象のシート名を右クリックし、表示されたメニューから「コードの表示」を選択します。

Excel VBAでドロップダウンリストから値を選択する2つの方法

  • その後、以下のコードをコピーして、コードウィンドウに貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB As String
On Error GoTo Exitsub
If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        ValueB = Target.Value
        Application.Undo
        ValueA = Target.Value
        If ValueA = "" Then
            Target.Value = ValueB
        Else
            Target.Value = ValueA & ", " & ValueB
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Excel VBAでドロップダウンリストから値を選択する2つの方法

  • このコードは実行せずに保存してください。
  • 次に、対象のワークシートに戻ります。セルD4に作成したドロップダウンリストをクリックすると、ドロップダウンから複数の値を選択できるようになっていることが確認できます(下記GIF参照)。

Excel VBAでドロップダウンリストから値を選択する2つの方法

上記のGIFを見るとわかるように、このVBAコードでは同じ特定の値を何度でも選択可能です。このセクションで紹介したマクロコードにより、ドロップダウンリストはあらゆる種類の値を選択できるようになります。

VBAコードの解説

Dim ValueA As String
Dim ValueB As String

変数名を定義しています。

On Error GoTo Exitsub

エラーが発生した場合は、ラベル「Exitsub」へジャンプします。

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

データの入力規則が設定されているセルD4を処理対象として設定します。入力規則を含むセルが存在しない場合は、ラベル「Exitsub」へジャンプします。

Else: If Target.Value = "" Then GoTo Exitsub Else

対象となるセルが空の場合は、ラベル「Exitsub」へジャンプします。そうでなければ、以降の行を実行します。

Application.EnableEvents = False

アプリケーションイベントをオフにすることで、Worksheet_Changeマクロの再発火を防止します。これを行わないと、無限ループが発生する可能性があります。

ValueB = Target.Value

変更されたセルの新しい値をValueBとして定義します。

Application.Undo

変更されたセルの操作を元に戻します。

ValueA = Target.Value

変更を元に戻すことで、変更前の古い値をValueAとして定義できます。

If ValueA = "" Then
    Target.Value = ValueB

古い値が空白の場合は、新しい値をそのまま格納します。

Else
    Target.Value = ValueA & ", " & ValueB
  End If
 End If
End If

それ以外の場合は、古い値と新しい値をカンマ(,)で連結して格納します。すべてのIfステートメントを閉じています。

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

アプリケーションイベントを再度オンに戻します。

関連記事: Excelで複数選択可能なドロップダウンリストを作成する方法

類似の読み物:

  • 別シートからドロップダウンリストを作成する方法(2つの方法)
  • 検索可能なドロップダウンリストをExcelで作成する(2つの方法)
  • Excelのドロップダウンリストが動作しないときの対処法(8つの問題と解決策)
  • テーブルからExcelのドロップダウンリストを作成する(5つの例)
  • Excelでドロップダウンリストを自動更新する(3つの方法)

方法2:重複しない値のみを選択できるようにするVBAマクロ

データセットには重複した値が含まれていますが、重複した値を除外して選択したい場合は、以下の手順に従ってください。

手順:

  • 先ほどと同様に、「開発」タブからVisual Basic Editorを開きます。
  • 次に、対象のワークシートを右クリックして表示される「コードの表示」オプションから、コードウィンドウを開きます。
  • その後、以下のコードをコピーして、指定したワークシートのコードウィンドウに貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$4" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    ValueB = Target.Value
    Application.Undo
    ValueA = Target.Value
      If ValueA = "" Then
        Target.Value = ValueB
      Else
        If InStr(1, ValueA, ValueB) = 0 Then
            Target.Value = ValueA & ", " & ValueB
      Else:
        Target.Value = ValueA
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Excel VBAでドロップダウンリストから値を選択する2つの方法

  • このコードは実行せずに保存してください。
  • 次に、対象のワークシートに戻ります。セルD4に作成したドロップダウンリストをクリックすると、ドロップダウンから複数の値を選択できるようになっていることが確認できます(下記GIF参照)。

Excel VBAでドロップダウンリストから値を選択する2つの方法

上記のGIFを見るとわかるように、このVBAコードでは同じ特定の値を複数回選択することはできません。このセクションで紹介したマクロコードにより、ドロップダウンリストは重複のない値のみを選択できるようになります。

VBAコードの解説

Dim ValueA As String
Dim ValueB As String

変数名を定義しています。

Application.EnableEvents = True

アプリケーションイベントをオンにします。

On Error GoTo Exitsub

エラーが発生した場合は、ラベル「Exitsub」へジャンプします。

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

データの入力規則が設定されているセルD4を処理対象として設定します。入力規則を含むセルが存在しない場合は、ラベル「Exitsub」へジャンプします。

Else: If Target.Value = "" Then GoTo Exitsub Else

対象となるセルが空の場合は、ラベル「Exitsub」へジャンプします。そうでなければ、以降の行を実行します。

Application.EnableEvents = False

アプリケーションイベントをオフにすることで、Worksheet_Changeマクロの再発火による無限ループの発生を防止します。

ValueB = Target.Value

変更されたセルの新しい値をValueBとして定義します。

Application.Undo

変更されたセルの操作を元に戻します。

ValueA = Target.Value

変更を元に戻すことで、変更前の古い値をValueAとして定義できます。

If ValueA = "" Then
    Target.Value = ValueB

古い値が空白の場合は、新しい値をそのまま格納します。

Else
    If InStr(1, ValueA, ValueB) = 0 Then
        Target.Value = ValueA & ", " & ValueB

InStr関数は、文字列内で部分文字列が最初に出現する位置を返します。結果が0の場合(=新しい値がまだ含まれていない場合)、古い値と新しい値をカンマ(,)で連結して格納します。

Else: Target.Value = ValueA
    End If
   End If
  End If
 End If

それ以外の場合(=新しい値がすでに存在する場合)は、古い値をそのまま維持します。すべてのIfステートメントを閉じています。

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

アプリケーションイベントを再度オンに戻します。

関連記事: 選択内容に応じて変化するExcelのドロップダウンリスト

まとめ

本記事では、VBAマクロを使用してExcelのドロップダウンリストから値を選択する方法を、2つの有効なアプローチで解説しました。重複を許可する方法と除外する方法のどちらも実務で役立つテクニックですので、ぜひ使い分けてみてください。この記事が皆さんのお役に立てば幸いです。トピックに関するご質問があれば、お気軽にお尋ねください。

関連記事

  • 数式に基づくドロップダウンリストをExcelで作成する方法(4つの方法)
  • Excelでの条件付きドロップダウンリスト(作成・並べ替え・活用)
  • IFステートメントを使ってExcelにドロップダウンリストを作成する方法
  • セルの値とドロップダウンリストを連動させる方法(5つの方法)
  • Excelでドロップダウンリストを削除する方法
  1. Excelでセルの値に基づいてドロップダウンリストを動的に変更する2つの方法

    特定の値をもとにデータを抽出したい場合、ドロップダウンリストが非常に便利です。さらに、複数の連動型（依存関係のある）ドロップダウンリストを作成できれば、選択内容に応じてリストの中身が自動的に切り替わる仕組みを実現できます。本記事では、Excelでセルの値に基づいてドロップダウンリストを変更する方法を、具体的な手順とともに詳しく解説します。 セルの値に基づいてドロップダウンリストを変更する2つの方法 以下では、最も実用的な2つの方法をご紹介します。1つ目は、OFFSET関数とMATCH関数を組み合わせて、セルの値に応じてリストを動的に変更する方法です。2つ目は、Microsoft 365（Exc

  2. Excelでドロップダウンリスト付きのデータ入力フォームを作成する2つの方法

    Microsoft Excelでは、データ入力フォームや計算フォームなど、さまざまな種類のフォームを作成できます。こうしたフォームを活用すれば、データ入力が格段に楽になり、作業時間の大幅な節約にもつながります。また、Excelには「ドロップダウンリスト」という便利な機能もあります。限られた値を何度も手入力するのは面倒ですが、ドロップダウンリストを使えば、リストから選ぶだけで簡単に値を入力できます。この記事では、Excelでドロップダウンリスト付きのデータ入力フォームを作成する方法を、具体的な操作画面とともにわかりやすく解説します。 Excelでドロップダウンリスト付きデータ入力フォームを