VBAでドロップダウンリストを設定～別シートのリストを参照する方法～

 

エクセルで入力規則のドロップダウンリストを設定しているのに、リストの選択肢を変えるたびに手作業で設定し直していませんか？

 

私も以前、商品名や担当者名のリストが変更になるたびに「データの入力規則」を開き直して、手動でリスト範囲を修正する作業を繰り返していました。

その作業だけで1回あたり10分以上かかることも。。。

 

VBAを使えば、別シートに管理しているリストの内容をドロップダウンに自動反映させることができます。

リストの中身が変わっても、マクロを1回実行するだけで設定が完了。

 

この記事では、Validation.Addメソッドと引数Targetの基本的な使い方から、別シートのリスト情報をドロップダウンに表示するVBAの実装方法まで、プログラミング初心者でもわかるように丁寧に解説していきます。

読み終えたころには「このコードを自分のエクセルにそのまま使える！」と感じてもらえるはずです。

 

ドロップダウンリストを手動で管理する3つの問題

エクセルの「データの入力規則」でドロップダウンリストを設定することは、多くの方が経験していると思います。

しかし、そのリストを手動で管理し続けるのは意外と非効率です。

VBAで自動化する前に、手作業がどれだけ手間なのかを整理しておきましょう。

 

問題①：リスト内容が変わるたびに設定を開き直している

商品の種類が増えた、担当者が変わった、選択肢を修正したい……そのたびに「データの入力規則」ダイアログを開き、リストの範囲や内容を書き直していては効率が悪いのでおすすめできません。

1件の修正なら数分で終わりますが、複数のシートに同じリストを設定しているケースでは、同じ作業を何度も繰り返すことになってしまいます。

 

これはVBAで簡単に自動化できる作業の典型例です。

 

問題②：リストの元データがシートのあちこちに散らばっている

ドロップダウンリストの元になるデータが、入力シートの片隅に直接書いてあったり、複数のシートにバラバラに存在していたりするケースは珍しくありません。

元データが整理されていないと、「どこのセルを参照しているのか」が分からなくなり、修正の際にミスが起きやすくなります。

 

リストのデータを「リスト管理シート」として1か所に集約し、VBAでそこを参照する仕組みを作れば、管理効率をアップさせることが可能です。

 

問題③：入力規則の設定が消えていても気づかない

エクセルの入力規則は、行や列を削除した際に意図せず消えてしまうことがあります。

気づかないまま入力が続くと、リスト外の値がセルに入り込み、データが乱れる原因になります。

 

VBAでドロップダウンを設定するコードを作っておけば、設定が消えてしまっても1クリックで元に戻すことが可能です。

 

VBAでドロップダウンリストを設定するための基礎知識

ここからは、ドロップダウンリストをVBAで設定するために必要な2つの知識を解説します。

Validation.Addメソッドと引数Targetを理解することが、今回のVBA実装の核心部分です。

 

Validation.Addメソッドの基本的な使い方

Validationオブジェクトは、セルに入力規則を設定するためのオブジェクトです。

その中のAddメソッドを使うことで、プログラムからドロップダウンリストを設定できます。

 

基本的な書き方は次の通りです。

Range("対象セル").Validation.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="リストの内容"

各引数の意味を説明します。

 

Type:=xlValidateList は「入力値の種類をリストにする」という指定です。

AlertStyle:=xlValidAlertStop は「リスト以外の値が入力されたら警告を出す」という設定で、入力ミスを防ぐ役割があります。

 

Formula1 には、ドロップダウンに表示したいリストの内容を指定します。

ここに別シートのセル範囲を文字列で指定するのがポイントです。

 

引数Targetとは何か

「引数Target」は、シート上でイベント（クリックやセルの変更など）が発生したときに、「どのセルで発生したのか」を受け取るための変数です。

WorksheetのChangeイベントやSelectionChangeイベントと組み合わせて使うことが多く、「特定のセルが選択されたタイミングでドロップダウンを設定する」という処理を実現するときに活用します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Targetには、クリックされたセルの情報が入っている
If Target.Column = 2 Then ' B列が選択されたとき
' ドロップダウンを設定する処理
End If
End Sub

引数Targetを使うことで、「このセルに触れたときだけドロップダウンを設定する」という細かい制御が可能になります。

 

別シートのリストを参照するドロップダウンVBAの実装方法

ここからが本記事のメインパートです。

実際に動くコードを紹介しながら、どのように処理が進むかを丁寧に解説していきます。

 

サンプル事例の設定

今回は以下のような構成のエクセルファイルを想定しています。

 

リスト管理シートには以下のような商品名が入力されているとします。

 

この構成で、入力シートのB列にドロップダウンを設定するVBAを作っていきます。

 

方法①：ボタンを押してドロップダウンを設定するVBA

まず、最もシンプルな方法として、ボタンを押したときにドロップダウンを設定するコードを紹介します。

Sub ドロップダウン設定()

Dim ws入力 As Worksheet
Dim wsリスト As Worksheet
Dim リスト範囲 As String
Dim 最終行 As Long

' シートを変数に代入する
Set ws入力 = ThisWorkbook.Worksheets("入力シート")
Set wsリスト = ThisWorkbook.Worksheets("リスト管理シート")

' リスト管理シートのA列の最終行を取得する
最終行 = wsリスト.Cells(wsリスト.Rows.Count, 1).End(xlUp).Row

' 別シートのセル範囲を文字列で作成する
' INDIRECT関数と組み合わせた書き方が必要
リスト範囲 = "リスト管理シート!$A$1:$A$" & 最終行

' 入力シートのB2からB100にドロップダウンを設定する
With ws入力.Range("B2:B100").Validation
' 既存の入力規則をクリアしてから設定する
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & リスト範囲
End With

MsgBox "ドロップダウンリストの設定が完了しました！", vbInformation

End Sub

コードの流れを解説します。

 

最初に「入力シート」と「リスト管理シート」をそれぞれ変数に代入しています。

Cells(Rows.Count, 1).End(xlUp).Row でリスト管理シートのA列の最終行番号を自動取得しているため、リストの行数が増えても自動的に対応可能。

 

リスト範囲 = “リスト管理シート!$A$1:$A$" & 最終行 で、別シートのセル範囲を文字列として作成しています。

.Delete で既存の入力規則を一度削除してから新しく設定し直すことで、二重設定によるエラーを防止することが可能。

 

Formula1:="=" & リスト範囲 の「=」を頭につけることがポイントで、これによってVBAがセル範囲の参照として認識してくれます。

 

方法②：セルを選択したときに自動でドロップダウンを設定するVBA

次に、引数Targetを活用して「B列のセルをクリックしたとき自動でドロップダウンを設定する」という方法を紹介します。

このコードは「入力シート」のシートモジュール（シートタブを右クリック→「コードの表示」）に記述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wsリスト As Worksheet
Dim 最終行 As Long
Dim リスト範囲 As String

' 複数セルが選択されている場合は処理しない
If Target.Count > 1 Then Exit Sub

' B列が選択されたとき、かつ2行目以降の場合のみ処理する
If Target.Column = 2 And Target.Row >= 2 Then

Set wsリスト = ThisWorkbook.Worksheets("リスト管理シート")
最終行 = wsリスト.Cells(wsリスト.Rows.Count, 1).End(xlUp).Row
リスト範囲 = "リスト管理シート!$A$1:$A$" & 最終行

' 選択されたセルにドロップダウンを設定する
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & リスト範囲
End With

End If

End Sub

コードの流れを解説します。

 

Worksheet_SelectionChange はシート上でセルの選択が変わるたびに自動実行されるイベントプロシージャです。

If Target.Count > 1 Then Exit Sub は複数セルを一度に選択した場合に処理をスキップさせます。

 

If Target.Column = 2 And Target.Row >= 2 Then で「B列、かつ2行目以降のセルが選択された場合のみ」という条件を絞り込んでいます。

この書き方のメリットは、ファイルを開いてB列をクリックするだけで自動的にドロップダウンが設定されるため、ボタンを用意する必要がないことです。

 

方法③：リストの内容に応じて動的にドロップダウンを切り替えるVBA

少し応用として、「A列で大カテゴリを選んだら、B列のドロップダウンが連動して変わる」という動的な切り替えVBAも紹介します。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsリスト As Worksheet
Dim 最終行 As Long
Dim リスト範囲 As String
Dim カテゴリ As String

' A列（2行目以降）が変更されたときのみ処理する
If Target.Count > 1 Then Exit Sub
If Not (Target.Column = 1 And Target.Row >= 2) Then Exit Sub

Set wsリスト = ThisWorkbook.Worksheets("リスト管理シート")

' A列に入力されたカテゴリ名を取得する
カテゴリ = Target.Value

' カテゴリに応じてリストの列を切り替える
Select Case カテゴリ
Case "果物"
最終行 = wsリスト.Cells(wsリスト.Rows.Count, 1).End(xlUp).Row
リスト範囲 = "リスト管理シート!$A$1:$A$" & 最終行
Case "野菜"
最終行 = wsリスト.Cells(wsリスト.Rows.Count, 2).End(xlUp).Row
リスト範囲 = "リスト管理シート!$B$1:$B$" & 最終行
Case "飲料"
最終行 = wsリスト.Cells(wsリスト.Rows.Count, 3).End(xlUp).Row
リスト範囲 = "リスト管理シート!$C$1:$C$" & 最終行
Case Else
' 未定義カテゴリの場合は同行のB列をクリアして終了
Me.Cells(Target.Row, 2).ClearContents
Me.Cells(Target.Row, 2).Validation.Delete
Exit Sub
End Select

' 同行のB列にドロップダウンを設定する
With Me.Cells(Target.Row, 2).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & リスト範囲
End With

End Sub

コードの流れを解説します。

 

Worksheet_Change はセルの値が変更されたときに自動実行されるイベントです。

A列に「果物」「野菜」「飲料」のどれが入力されたかを Select Case で判定し、それぞれに対応したリスト管理シートの列（A列・B列・C列）をドロップダウンの参照先として切り替えています。

 

Me.Cells(Target.Row, 2) の「Me」は「このシート自身」を指しており、A列と同じ行のB列セルにドロップダウンを設定することが可能。

これにより、大カテゴリの選択に応じて、小カテゴリのドロップダウンが自動的に切り替わる入力フォームが完成します。

 

VBAでドロップダウンを設定する際の注意点

実装の際につまずきやすいポイントを3つまとめます。

知っておくだけでエラー解決の時間をぐっと短縮できます。

 

注意点①：別シート参照はシート名の指定方法に気をつける

Formula1 に別シートのセル範囲を指定する際、シート名にスペースや特殊文字が含まれている場合は、シート名をシングルクォーテーションで囲む必要があります。

' シート名に空白やスペースが含まれる場合
リスト範囲 = "'リスト 管理シート'!$A$1:$A$" & 最終行

シート名に問題がないと思っていても「1004エラー」が出る場合は、まずここを確認してみましょう。

 

注意点②：Validation.Deleteを必ず入れる

既存の入力規則が設定されているセルに対して .Add だけを実行すると、エラーが発生することがあります。

.Delete で一度クリアしてから .Add で設定し直す、という順番を守ることが重要です。

 

この2行はセットで使う習慣をつけておくと安全です。

 

注意点③：Worksheet_ChangeとWorksheet_SelectionChangeの使い分け

今回紹介した2つのイベントには、使い分けが必要な場面があります。

Worksheet_SelectionChange はセルを選択するたびに実行されるため、頻繁にドロップダウンの設定処理が走ることになります。

一方、Worksheet_Change はセルの値が変更されたときだけ実行されるため、連動ドロップダウンのように「選択した値に応じて次のリストを変える」用途に向いています。

 

どちらを使うかは、実装したい動作に合わせて選ぶようにしましょう。

 

VBAスキルを身につけたら副収入へ

ここまで読んでくれた方は、VBAで別シートのリストを参照するドロップダウンの設定方法を理解できたと思います。

実は、このVBAスキルは業務効率化だけでなく、副収入を得ることにも十分活用できます。

 

私自身の体験をお伝えします。

VBAでドロップダウンリストの自動化や入力フォームの構築ができるようになったころ、「このスキルを収入につなげられないか」と考え始めました。

 

ココナラの利用

最初に登録したのがスキルを売買できるプラットフォーム「ココナラ」です。

登録したばかりの頃は実績ゼロなので、まったく仕事が来ませんでした。

そこで自己紹介文を書き直し、ポートフォリオとして自分が作ったVBAサンプルを画像と動画で掲載。

単価が低くても作業をこなして実績数を積み重ねることで、少しずつ信頼度を上げていきました。

 

ひたすら地道な作業でしたが、登録から1年ほど経ったころ、VBAマクロ開発の依頼を受注できるようになり、月に2万〜3万円の副収入を継続的に得られるようになりました。

リピートしてくれる依頼者も増え、「あのドロップダウンのマクロをほかのシートにも設定したい」という追加依頼をいただくことも。

 

エクセルマクロ開発の直接受注

次に試みたのが、エクセルVBAのブログで情報発信しブログ経由で直接マクロ開発を受注するという方法です。

ブログからの直接受注はプラットフォームの手数料がかからないため、受け取れる報酬が増えるというメリットがあります。

結果として月に3万〜4万円の収入を得ることができました。

 

クラウドワークステックの利用

その後、さらに収入の幅を広げたいと考え、クラウドワークス テックにも登録してみました。

私の場合は「本業以外の時間（1日4時間稼働・完全リモート）」という条件を担当者に伝え、その条件に合った案件を紹介してもらいました。

事前のヒアリングがしっかりしており、「どんな仕事なのか」「自分に務まるのか」を担当者と一緒に確認してから案件を受けることができます。

月に10万円程度の報酬を受け取ることができましたが、仕事の内容によっては本業との掛け持ちがきつく感じることもあり、現在は利用していません。

 

VBAスキルで副業を始めるなら、「報酬金額」だけでなく「自分のペースで続けられるかどうか」を必ず検討しましょう。

 

関連記事「【体験談あり】VBAスキルで副収入を得る方法｜未経験から案件獲得まで」では、私がゼロから副収入を得るまでの具体的なステップをまとめていますので、ぜひ合わせて読んでみてください。

 

 

まとめ ～ VBAで別シートのリストを参照するドロップダウンを使いこなそう ～

この記事では、VBAでドロップダウンリストを設定するための基本知識と、別シートのリストを参照する3つの実装方法を解説しました。

 

Validation.AddメソッドとFormula1に別シートのセル範囲を文字列で指定することが、今回の実装の核心部分です。

引数Targetを組み合わせることで、「特定のセルを選択したときだけ設定する」「値の変更に連動してドロップダウンを切り替える」という柔軟な制御も実現できます。

 

まずは今回紹介したコードをそのままコピペして、自分のエクセルで動かしてみるところから始めてみましょう。

動いた瞬間の感覚を覚えておくことが、VBAを続けるいちばんの原動力になります。

 

もし「自分の業務に合わせた複雑なチェック処理を実装したい」「既存のマクロを改良したい」といったご要望がありましたら、マメBlogのエクセルマクロ開発依頼にご相談ください。

 

 

業務内容に合わせたオーダーメイドマクロを設定させていただきます。

 

最後まで読んでいただきありがとうございました。

エクセルVBAを使って面倒なルーティーン作業を自動化しちゃいましょう。