Excel の VBA でデータ検証リストに名前付き範囲を使用する方法

データ検証に名前付き範囲を使用する最も簡単な方法を探している場合 Excel VBA のリスト 、その後、この記事が役立つことがわかります。名前付き範囲は、ドロップダウン リストを簡単に作成するためにデータ検証式で使用すると便利です。このタスクは、いくつかの VBA の助けを借りて非常に簡単に行うことができます。 コード。
それでは、メインの記事を開始して、データ検証リストで名前付き範囲を使用する方法を調べてみましょう.

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

Excel で VBA を使用してデータ検証リストに名前付き範囲を使用する 4 つの方法

ここには、いくつかの製品のレコードとそれぞれの販売員のリストを含む次のデータセットがあります。このデータセットを使用して、さまざまな VBA でさまざまな方法を示します。 データ検証リストで名前付き範囲を使用するためのコード

Microsoft Excel 365 を使用しています 必要に応じて他のバージョンを使用できます。

方法-1 :ドロップダウン リストを作成するためのデータ検証での名前付き範囲の使用

ここでは、 フルーツ の範囲に名前を付けました 果物のコラム VBA を使用する セル D6 にドロップダウン リストを作成するコード .

ステップ-01 :
➤ デベロッパー に移動します タブ>> Visual Basic オプション。

次に、 Visual Basic Editor 開きます。
➤ 挿入に移動します タブ>> モジュール オプション。

その後、モジュール 作成されます。

ステップ-02 :
➤ 次のコードを記述

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

ここで、検証 セル D6 に追加されます , xlValidateList はドロップダウン リストを作成するためのもので、式は範囲の名前として使用されます “=Fruits” .

➤ F5 を押します その後、セル D6 のドロップダウン記号をクリックします .
次に、果物のリストを取得し、リストから チェリー などのアイテムを 1 つ選択します。 .

最後に、セル D6 で選択したアイテムを取得します .

続きを読む:Excel で表からデータ検証リストを作成する方法 (3 つの方法)

方法-2 :名前付き範囲と VBA コードを使用したデータ検証リストの追加

ここでは、単純な VBA ではなく、手動で名前付き範囲を作成しません。 コードは名前付き範囲を形成し、それを使用して最終的にセル D6 でドロップダウン リストを取得します .

歩数 :
➤ Step-01 に従ってください 方法-1の .
➤ 次のコードを記述

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

まず、フルーツ という名前を追加します。 範囲 「B4:B10」 ワークシートの 追加 .
次に、検証を追加します セル D6 へ , xlValidateList ドロップダウンリストを作成するためのもので、数式は範囲の名前として使用されます “=Fruit” .

➤ F5 を押します 、次に、ワークシートに移動し、セル D6 のドロップダウン記号をクリックします .
後で、果物のリストを取得し、リストから ブルーベリー などのアイテムを 1 つ選択します。 .

目的のアイテム ブルーベリー を手に入れました リストから、これに加えて、作成した果物の名前付き範囲を確認できます。

関連コンテンツ:Excel での複数選択によるデータ検証ドロップダウン リストの作成

類似の読み方:

• Excel のオートコンプリート データ検証ドロップダウン リスト (2 つの方法)
• フィルター付きの Excel データ検証ドロップダウン リスト (2 つの例)
• Excel VBA (マクロおよびユーザー フォーム) を使用したデータ検証リストの既定値
• Excel で複数の条件にカスタム データ検証を適用する (4 つの例)
• Excel データ検証 英数字のみ (カスタム数式を使用)

方法-3 :Excel VBA を使用して名前付き範囲でデータ検証リストを更新する

セル D6 に次のドロップダウン リストがあるとします。

しかし、追加の野菜 レタス を追加すると その場合、ドロップダウン リストに表示されません。つまり、この場合、ドロップダウン リストは自動的に更新されません。

リストをすばやく自動的に更新するには、この方法に従います。

3.1:更新された名前付き範囲の作成

まず、Column の範囲の名前を追加する必要があります B 新しく追加されたアイテムがこの名前に自動的に取り込まれるようにします。
➤ 式に移動します タブ>> 定義済みの名前 グループ>> ネーム マネージャー オプション。

次に、 ネーム マネージャー ダイアログボックスが開きます。
➤ [新規] をクリックします。 オプション。

その後、 名前の編集 ウィザードがポップアップします。
➤ 野菜を書き留めます 名前 ボックスと Refers to の次の数式 ボックスに入力し、最後に OK を押します .

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

ほら、アップデート！ シート名は $B$4 です 0 は、移動元の参照セルです。 行 および 列 引数は、参照位置または開始位置に留まることを意味します。
カウント 列 B の任意のタイプの値を持つセルの数をカウントします そして 2 B1 のデータセットの見出しのため、 が差し引かれます B3 の列のヘッダー .したがって、野菜を含むセルの数だけが得られます。
この数値は、開始位置と OFFSET からのリターン リファレンスになります。 は常に更新された名前付き範囲をここに返します。

次に、 Name Manager に移動します。 ウィザード。
➤ 閉じるを押します。

3.2:VBA コードを使用してデータ検証リストを適用する

➤ シート名を右クリックし、[コードの表示] を選択します。 オプション。

その後、コード ウィンドウが表示されます。

➤ 次のコードを入力してください

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

このコードは、値の変更または追加が発生した場合にのみ実行されるため、手順を Worksheet_Change として定義しました。 、 ワークシート は オブジェクト です と 変更 は手順です .
新しいアイテム 新しい値を追加するセルのアドレスが含まれており、それを Range として定義しました . updatedrange のデータ型 とアイテム updatedrange を割り当てたバリアントと見なされます 野菜と項目の更新された名前付き範囲に この範囲の各セルの値です。
FOR ループは更新された範囲を updatedrange に割り当てます そして WITH ステートメントは同じオブジェクトの繰り返しを回避し、最後に validation を追加しました .

ここで、メイン シートに戻り、製品 レタス を追加した後の効果を確認します。 .
ご覧のとおり、ドロップダウン リストにこの新しいアイテムが追加されました。

この新しい項目を選択すると、セル D6 に表示されます .

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

方法-4 :名前付き範囲を使用して条件付きドロップダウン リストを作成する

ここでは、セル E6 にドロップダウン リストを作成します。 これはセル D6 の状態に依存します このため、 fruit1 など、次の 2 つの名前付き範囲があります。 と野菜1 .

歩数 :
➤ Step-01 に従ってください 方法-1の .
➤ 次のコードを記述

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

IF-THEN ステートメントは、セル D6 の値が 果物です この値に対して、名前付き範囲 fruit1 をリストとして取得します セル E6 のリストとして それ以外の場合は、名前付き範囲 vegetable1 を取得します セル E6 のリストとして .

➤ F5 を押します 、次にワークシートに移動し、セル E6 のドロップダウン記号をクリックします .
その後、カテゴリの果物のリストを Fruits として取得します。 セル内 D6 ブラックベリーなどのリストからアイテムを 1 つ選択します .

目的のアイテム ブラックベリー を手に入れました リストから。

➤ カテゴリを 野菜 に変更する場合 コードを実行すると、野菜のリストが表示されることがわかります。

ブロッコリー を選択した後 セル E6 でこのアイテムを取得しています .

関連コンテンツ: Excel のデータ検証式で IF ステートメントを使用する方法 (6 つの方法)

練習セクション

自分で練習するために、練習を提供しています Practice という名前のシートで以下のようなセクション .自分でやってください。

結論

この記事では、Excel VBA でデータ検証リストに名前付き範囲を使用する方法について説明しようとしました。 簡単に。役に立つことを願っています。提案や質問がある場合は、コメント セクションで自由に共有してください。

関連記事

• 色付きの Excel でデータ検証を使用する方法 (4 つの方法)
• 配列からデータ検証リストを作成する Excel VBA
• Excel データ検証でカスタム VLOOKUP 数式を使用する方法
• [修正] Excel でのコピー貼り付けでデータ検証が機能しない (ソリューションあり)
• Excel のデータ検証リストから空白を削除する方法 (5 つの方法)