Excel の VBA を使用したデータ検証ドロップダウン リスト (7 アプリケーション)

データ検証ドロップダウン リストは、さまざまな Excel 関連のタスクを実行するための非常に便利な機能です。 VBA の実装 は、Excel で操作を実行するための最も効果的、迅速、かつ安全な方法です。この記事では、データ検証ドロップダウン リストの 7 つの異なるアプリケーションを紹介します。 VBA マクロを使用して Excel で .

ワークブックをダウンロード

ここから無料の練習用 Excel ワークブックをダウンロードできます。

Excel のデータ検証ドロップダウン リストに VBA を使用した 7 つのメソッド

このセクションでは、データ検証ドロップダウン リストの 7 つの異なるアプリケーションについて学習します。 VBA を使用した Excel マクロ。

1. VBA を埋め込んで Excel でデータ検証ドロップダウン リストを作成する

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

手順:

• 最初に Alt + F11 を押します キーボードで、またはタブ Developer -> Visual Basic に移動します Visual Basic Editor を開く .

• 次に、ポップアップ コード ウィンドウで、メニュー バーから [挿入] -> [モジュール] をクリックします。 .

• その後、コピー 次のコードと貼り付け それをコード ウィンドウに表示します。

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

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

このコードは、セル B5 にドロップダウン リストを作成します。 .ドロップダウン リストには「グレープ、オレンジ、グアバ、マンゴー、アップル」という値が含まれます。 「.

• では、 F5 を押してください キーボードまたはメニュー バーから Run -> Run Sub/UserForm を選択します . スモール ラン アイコンをクリックすることもできます。 サブメニュー バーでマクロを実行します。

コードの実行後、下の画像を見て結果を確認してください。

上の画像からわかるように、Cell B5 にドロップダウン リストが作成されています。 値は「グレープ、オレンジ、グアバ、マンゴー、アップル」 「.

続きを読む: データ検証用の Excel ドロップダウン リストの作成方法 (8 つの方法)

2. Excel で VBA を使用して名前付き範囲でドロップダウン リストを生成する

ドロップダウン リストのすべての値をコード内に記述したくない場合は、すべての名前を 定義された名前 内に入れることができます 後でそれを使用して、値の範囲を呼び出します。これは、Excel でドロップダウン リストを作成するための非常に便利な方法です。

このセクションでは、名前付き範囲を使用して特定のリストからドロップダウン リストを生成する方法を学習します。 VBA で コード。

手順:

• まず、範囲を選択します ドロップダウン リストの値が存在する場所 (この場合、範囲は B5:B9 です) ).
• 次に、右クリック
• オプションのリストが表示されます。そこから、[名前を定義…] を選択します。

• その後、新しい名前 ポップアップボックスが表示されます。 名前 ボックスに任意の名前を記入 あなたが好きなもの (セルに Fruits という名前を付けました ).
• 後で、[OK] をクリックします .

• 範囲に名前を付けました B5:B9 フルーツ (下の写真を参照)

この VBA コードで定義された名前を使用します。 .その手順を以下に示します。

• 前と同じように、Visual Basic Editor を開きます 開発者から タブと挿入 モジュール コード ウィンドウで。
• 次に、コード ウィンドウで コピー 次のコードと貼り付け

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

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

このコードは、セル B12 にドロップダウン リストを作成します。 値は「グレープ、オレンジ、グアバ、マンゴー、アップル」 」という名前で定義されている Fruits .

• 後で、実行 マクロ。結果を下の画像に示します。

その結果、上の画像から、セル B12 に作成されたドロップダウン リストがあることがわかります。 値は「グレープ、オレンジ、グアバ、マンゴー、アップル」 「.

続きを読む: 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

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

このコードは、セル B12 にドロップダウン リストを作成します。 B5:B9 の範囲にある値 .

• さあ、走る マクロを実行し、次の画像を見て出力を確認してください。

その結果、上の画像から、セル B12 に作成されたドロップダウン リストがあることがわかります。 値は「グレープ、オレンジ、グアバ、マンゴー、アップル」 」をセル B5 から B9 に保存しました

関連コンテンツ: Excel VBA (マクロおよびユーザー フォーム) を使用したデータ検証リストの既定値

類似の読み方:

• フィルター付きの Excel データ検証ドロップダウン リスト (2 つの方法)
• 英数字のみのデータ検証 (カスタム式を使用)
• 別のセル値に基づく Excel データの検証
• Excel データ検証でカスタム VLOOKUP 数式を使用する方法

4. Excel で複数のドロップダウン リストを作成するための VBA の実装

作成できます 複数のドロップダウン リスト VBA を使用 大きい。 Excel でそれを行う方法を見てみましょう。

手順:

• まず、Visual Basic Editor を開きます 開発者から タブと挿入 モジュール コード ウィンドウで。
• 次に、コード ウィンドウで コピー 次のコードと貼り付け

Sub MultipleDropDownList(iTarget As Range, iSource As Range)
    'to delete and add validation in the target 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. VBA を適用してユーザー定義関数を使用してドロップダウン リストを作成する

ユーザー定義関数 (UDF) を使用してドロップダウン リストを作成することもできます

それを取得する手順を以下に示します。

手順:

• 最初に、シートを右クリックします。 UDF を実装する場所 ドロップダウン リストを作成します。
• 次に、[コードを表示] を選択します 出てきたリストから。以下に示すように、右クリックしました UDF という名前のシートで データセットが保存され、選択されている場所 コードを表示 オプションから。

• 次に、コピー 次のコードと貼り付け 自動生成コード ウィンドウに表示されます。

Public Function DropDownUDF(iSource As Range) As Variant
    'to delete and add validation in the specified range
    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
    'this will return the first value
    'this reset the values when formula in sheet are refreshed
    DropDownUDF = VBA.Val(iSource(1))
End Function

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

• その後、ワークシートに戻ります
• 続いて、任意のセルを選択 ドロップダウン リストを作成する場所 (この場合、Cell B11 です) ).
• そのセルに、新しく作成した関数を記述します – DropDownUDF – 他の関数の書き方。つまり、最初に等号 (=) を入力します 、次に関数名、DropDownUDF を書きます セル参照を渡す (B5:B9 ) を括弧内に挿入します。

セル B11 の数式 次のようになります:

=DropDownUDF(B5:B9)

• 次に、Enter を押します .

その結果、UDF によって作成されたドロップダウン リストが得られます セル B11 値は「グレープ、オレンジ、グアバ、マンゴー、アップル」 」の範囲に格納されている B5:B9 そして、関数内で渡しました。

続きを読む: Excel のデータ検証式で IF ステートメントを使用する方法 (6 つの方法)

6. VBA を使用してドロップダウン リスト内の別のシートからデータを抽出する

次の画像を見てください。 List という名前のシートにデータセットがあります .

ここで行うことは、セル B5 にドロップダウン リストを作成することです Target という名前のシートの （下の写真を参照）。そのドロップダウン リストの値は、範囲 B5:B9 の値になります。 リストの

Let’s see the steps on how to do that with VBA .

手順:

• Firstly, open Visual Basic Editor 開発者から タブと挿入 モジュール コード ウィンドウで。
• Then, copy the following code and paste it into the code window.

Private Sub DropDownFromSheet()
'to store the dropdown list in cell B5
'you can replace "B5" with any other cell
With Range("B5").Validation
.Delete
'to extract data from "List" sheet and "B5:B9" range
'you can replace "=List!B5:B9" with your sheet name and range
.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

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

• Later, Run the macro and look at the following image to see the output.

As a result of the successful code execution, there is a dropdown list created in Cell B5 of the Target worksheet with the values “Grapes, Orange, Guava, Mango, Apple ” that are stored in range B5:B9 of the List spreadsheet.

続きを読む: 別のシートからデータ検証リストを使用する方法 (6 つの方法)

7. Deleting Data Validation Drop Down List from Excel with VBA Macro

This section will show you how to delete a dropdown list from Excel. We will show you how to delete the existing dropdown list in Cell B5 ( shown in the image below) with the VBA マクロ。

The steps to execute that are given below.

手順:

• At first, open Visual Basic Editor 開発者から タブと挿入 モジュール コード ウィンドウで。
• Then, in the code window, copy the following code and paste

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

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

• Later, Run the macro and look at the following image.

As you can see from the above image is that there is no dropdown list anymore in Cell B5 . Finally, we have learnt how to delete an existing dropdown list from a spreadsheet with VBA .

続きを読む: Excel のデータ検証リストから空白を削除する方法 (5 つの方法)

結論

To conclude, this article showed you 7 different applications of the data validation drop-down list VBA マクロを使用して Excel で .この記事があなたにとって非常に有益であることを願っています。このトピックに関する質問はお気軽にどうぞ。

関連記事

• [修正] Excel でのコピー貼り付けでデータ検証が機能しない (ソリューションあり)
• How to Use Named Range for Data Validation List with VBA in Excel
• 配列からデータ検証リストを作成する Excel VBA
• 色付きの Excel でデータ検証を使用する方法 (4 つの方法)
• Apply Multiple Data Validation in One Cell in Excel (3 Examples)