Excel VBAでデータの入力規則リストに名前付き範囲を活用する4つの方法

Excel VBAでデータの入力規則リストに名前付き範囲を簡単に使う方法をお探しの方に、この記事はきっと役立ちます。名前付き範囲を入力規則の数式に使えば、ドロップダウンリストを手軽に作成できます。さらに、VBAコードを活用すれば、この作業は格段に効率化できます。
それでは、データの入力規則リストで名前付き範囲を活用するさまざまな方法を見ていきましょう。

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

Excel VBAでデータの入力規則リストに名前付き範囲を使う4つの方法

ここでは、いくつかの商品とそれぞれの担当販売者のリストを含む次のようなデータセットを用意しました。このデータセットを使って、異なるVBAコードによる複数の方法で、データの入力規則リストに名前付き範囲を使用する手順をご紹介します。

本記事ではMicrosoft Excel 365を使用していますが、お使いの環境に合わせて他のバージョンでも問題なく実行できます。

方法1：データの入力規則で名前付き範囲を使ってドロップダウンリストを作成する

ここでは、Fruits列の範囲にFruitsという名前を付けており、VBAコードを使ってセルD6にドロップダウンリストを作成します。

ステップ1：
➤ 開発タブ >> Visual Basic をクリックします。

すると、Visual Basic Editor（VBE）が開きます。
➤ 挿入タブ >> 標準モジュールを選択します。

これでモジュールが作成されます。

ステップ2：
➤ 次のコードを入力します。

Sub Datavalidation1()

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

End Sub

このコードでは、セルD6に入力規則が追加され、xlValidateListによってドロップダウンリストが作成されます。数式には範囲の名前である「=Fruits」を指定しています。

➤ F5キーを押してコードを実行し、その後セルD6のドロップダウンボタンをクリックします。
すると果物のリストが表示されるので、Cherries（さくらんぼ）など任意の項目を選択してみましょう。

最終的に、選択した項目がセルD6に表示されます。

関連記事：Excelでテーブルからデータの入力規則リストを作成する方法（3つの方法）

方法2：VBAコードで名前付き範囲と入力規則リストを同時に追加する

この方法では、名前付き範囲を手動で作成しません。シンプルなVBAコードが名前付き範囲を自動的に作成し、それを使ってセルD6にドロップダウンリストを作成します。

手順：
➤ 方法1のステップ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

まず、ワークシートAddの範囲「B4:B10」にFruitという名前が追加されます。
次に、セルD6に入力規則が追加され、xlValidateListによってドロップダウンリストが作成され、数式として範囲の名前「=Fruit」が使用されます。

➤ F5キーを押してコードを実行したら、ワークシートに戻り、セルD6のドロップダウンボタンをクリックします。
すると果物のリストが表示されるので、Blueberries（ブルーベリー）など任意の項目を選択しましょう。

これで、リストから目的の項目Blueberriesを選択できました。また、作成された果物の名前付き範囲も確認できます。

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

あわせて読みたい：

• Excelでデータの入力規則ドロップダウンリストのオートコンプリートを使う方法（2つの方法）
• フィルター付きのExcelデータの入力規則ドロップダウンリスト（2つの例）
• Excel VBAでデータの入力規則リストに初期値を設定する（マクロとユーザーフォーム）
• Excelで複数条件に対応するカスタムデータの入力規則を適用する（4つの例）
• Excelで英数字のみ入力可能にするデータの入力規則（カスタム数式を使用）

方法3：Excel VBAで名前付き範囲を使って入力規則リストを自動更新する

たとえば、セルD6に次のようなドロップダウンリストがあるとします。これは固定のデータセットに対しては正常に機能します。

しかし、新しい野菜Lettuce（レタス）を追加しても、ドロップダウンリストには表示されません。つまり、この状態ではリストは自動更新されないということです。

リストを素早く自動的に更新したい場合は、この方法を参考にしてください。

3.1：自動更新される名前付き範囲の作成

まず、B列の範囲に名前を付けます。このとき、新しく追加された項目が自動的にその名前に含まれるように設定します。
➤ 数式タブ >> 定義された名前グループ >> 名前の管理をクリックします。

すると、名前の管理ダイアログボックスが開きます。
➤ 新規ボタンをクリックします。

続いて、新しい名前ダイアログが表示されます。
➤ 名前ボックスにVegetablesと入力し、参照範囲ボックスに以下の数式を入力して、OKをクリックします。

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

ここで、Update!はシート名、$B$4は基準となる参照セルです。行と列の引数に0を指定しているため、開始位置から移動しません。
COUNTA関数はB列内の値が入力されているセルの数をカウントし、そこから2を引きます。これはB1のデータセット見出しとB3の列ヘッダー分を除くためです。こうすることで、野菜が入力されているセルの数だけを取得できます。
この数値が開始位置からの戻り参照となり、OFFSET関数により常に最新の名前付き範囲が返される仕組みです。

その後、名前の管理ダイアログに戻ります。
➤ 閉じるをクリックします。

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として定義しています。Worksheetがオブジェクト、Changeがイベントプロシージャです。
newitemには新しい値を入力したセルのアドレスが格納され、Range型として定義されています。updatedrangeとitemのデータ型はVariantとして扱われ、updatedrangeには更新後の野菜の名前付き範囲が、itemにはその範囲内の各セルの値が代入されます。
FORループで更新後の範囲をupdatedrangeに代入し、WITHステートメントで同じオブジェクトの記述を省略しながら、最後に入力規則を追加しています。

それでは、メインのシートに戻り、商品Lettuce（レタス）を追加した後の効果を確認してみましょう。
ご覧のとおり、新しい項目がドロップダウンリストに表示されました。

この新しい項目を選択すると、セルD6に反映されます。

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

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

ここでは、セルD6の値に応じて内容が変わるドロップダウンリストをセルE6に作成します。そのために、fruit1とvegetable1という2つの名前付き範囲を用意しました。

手順：
➤ 方法1のステップ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の値がFruitsかどうかを判定します。値がFruitsであれば、セルE6のリストとして名前付き範囲fruit1が設定され、そうでなければ名前付き範囲vegetable1が設定されます。

➤ F5キーを押してコードを実行したら、ワークシートに戻り、セルE6のドロップダウンボタンをクリックします。
セルD6のカテゴリがFruitsの場合は果物のリストが表示されるので、Blackberries（ブラックベリー）など任意の項目を選択しましょう。

これで、リストから目的の項目Blackberriesを選択できました。

➤ カテゴリをVegetablesに変更してコードを実行すると、今度は野菜のリストが表示されます。

Broccoli（ブロッコリー）を選択すると、その項目がセルE6に反映されます。

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

練習セクション

ご自身で練習できるよう、Practiceという名前のシートに以下のような練習セクションを用意しています。ぜひ自分で試してみてください。

まとめ

この記事では、Excel VBAでデータの入力規則リストに名前付き範囲を簡単に活用する方法をご紹介しました。皆さんの業務に役立てていただければ幸いです。ご提案やご質問があれば、ぜひコメント欄でお気軽にお知らせください。

関連記事

• Excelで色を使ったデータの入力規則を適用する方法（4つの方法）
• Excel VBAで配列からデータの入力規則リストを作成する
• Excelのデータの入力規則でカスタムVLOOKUP数式を使う方法
• [解決済み] Excelでコピー＆ペースト時にデータの入力規則が機能しない問題の対処法
• Excelでデータの入力規則リストから空白を削除する方法（5つの方法）