VBAでドロップダウンリストを設定する方法【5つのサンプルVBAを使って設定方法を徹底解説】

疑問がある人

VBAを使ってドロップダウンリストを設定することはできるの？VBAを使えばどんなドロップダウンリストが出来上がるの？

こんな疑問にお答えします。

結論から言うと、VBAを使ってドロップダウンリストを設定することは可能。VBAを応用すればリスト内容を自動的で変化するドロップダウンリストを作成することができる！です。

結論が少し長くなってしまいましたが、私の個人的な考えとしては「ドロップダウンリストはVBAを使って設定した方がより便利に利用することができる」ということです。

この記事では、VBAを使ってドロップダウンリストを設定する基本的な方法からちょっと複雑なプログラムを取り入れて仕事で使えるドロップダウンリストの設定する方法をご紹介しています。

ドロップダウンリストを取り入れてエクセル作業の効率をアップさせたいと考えている方は是非参考にしてみてください。

1 そもそもドロップダウンリストってなに？
2 VBAを使ってドロップダウンリストの設定をする方法
- 2.1 validation.addメソッドとは
- 2.2 引数Targetとは
3 ドロップダウンリストを設定するためのサンプル事例
4 VBAを使ったドロップダウンリストの設定は外注依頼がおすすめ

そもそもドロップダウンリストってなに？

そもそもドロップダウンリストとはセルに直接テキストを入力するのではなく、指定するリストを呼び出し、リストの中からテキストを選択しセルにに入力できるというものです。

困った人

ドロップダウンリストは他にもプルダウンと言わればすが、どっちの方が正しいのか、違いがあるのかはわかりません。。

下図のように、ドロップダウンリストが設定されたセルをクリックするとリストが表示されます。

表示されたリストから入力したい項目をクリックするだけでテキストを入力することができます。

同じ入力内容の繰り返し！ある程度決まっている用語しか入力することがない！という場合にドロップダウンリストの設定が有効です。

VBAを使ってドロップダウンリストの設定をする方法

VBAを使ってドロップダウンリストを設定するにはvalidation.addメソッドと引数Targetを利用します。この二つのVBAを組み合わせることで「セルをクリックしてドロップダウンリストを表示させる」という処理が可能になります。

validation.addメソッドと引数Targetについて解説していきます。

validation.addメソッドとは

validation.addメソッドとは、指定した範囲にデータの入力規則を追加するというVBAです。

Validation.Add メソッドの構文

式.Add (Type, AlertStyle, Operator, Formula1)

Validation.Add メソッドのパラメータ

Type	必須	入力規則の種類を指定します。
AlertStyle	省略可能	入力規則でのエラーのスタイルを指定します。
Operator	省略可能	データ入力規則の演算子を指定します。
Formula1	省略可能	データ入力規則での条件式の最初の部分を指定します。

参考書やネットで「VBAを使ったドロップダウンリストの設定方法」を調べると上記のように、構文やパラメータの解説記事がほとんどだと思います。

ダウンする人

正直、構文やパラメータと言われてもよくわからない。。。

構文やパラメータについて一生懸命勉強するよりも、いくつかのサンプルVBAを見比べたほうがValidation.Add メソッドについて理解できると思います。この記事の途中でいくつかの事例に対するサンプルVBAを掲載しているので、サンプルVBAと上記の構文・パラメータを見比べてValidation.Add メソッドのイメージを膨らめせてみてください。

引数Targetとは

参考書やネットを使って引数Targetの定義を調べてみたのですが、明確なことはよくわかりませんでした。定義を理解していなくても引数Targetを使いこなすことは十分可能です。

今回は、「指定するセル(ターゲットしたセル)をクリックすると、ドロップダウンリストを表示させる」という使い方をします。

引数Targetは、「指定した範囲でなんらかのイベントが発生したときに、なんらかの処理をする」というときに使われるプログラムなのでドロップダウンリストを表示させるVBAにはうってつけのプログラムです。

下記の記事では、引数Targetの使い方について詳しくご紹介しています。

mamemametochan.com

VBAの引数Targetの使い方【セルのクリックでVBAを動作させる方法】

https://mamemametochan.com/excel-34

豆父ちゃんセルをクリックして作り上げたVBAの実行したい！こんな疑問にお応えします。結論から言うと、引数Targetを使ってVBAを作る！です。VBAの実行方法でよく使われるのが、ユーザーフォームによる実行やボタンによる実行です。これらの表示が邪魔くさい！と言う方に引数TargetによるセルをクリックしてVBAを実行させる方法はとてもおすすめです。セルをクリックして実行させる方法はいろんなことに応用することができます。私の職場では引数Targetが使われたVBAを数多く導入しています。・色を変化させ、チェック作業に応用・内容...

併せて確認してみてください。

ドロップダウンリストを設定するためのサンプル事例

いくつかのサンプル事例を見比べることでドロップダウンリストの設定に関するVBAの理解を深めることができます。下記では、決められたリスト内容を表示させる簡単な事例から条件によってリスト内容が変化する難しい事例を紹介しています。

ひらめく人

基本的には上記で解説したValidation.Add メソッドと引数Targetから作られています。事例を比較してドロップダウンリストの設定に関するVBAをマスターしましょう。

サンプル事例① セルをクリックすると簡単なリストを表示させる

サンプル事例①は以下の内容になります。

シート内であればどこのセルをクリックしてもドロップダウンリストが表示される
VBAに直接リスト内容を記入する(〇と×を表示させる)
セルにはリスト以外のテキストを入力できる設定にする

上記の条件でサンプルVBAを作成していきます。

1.VBAを記入する場所を選択

2.サンプルVBAをコピペする

1	Private Sub Worksheet_SelectionChange(ByVal Target As Range)
2	ActiveSheet.Cells.Validation.Delete
3	With Target.Validation
4	.Add Type:=xlValidateList, Formula1:="〇,×"
5	.ShowError = False
6	End With
7	If Err <> 0 Then
8	Err.Clear
9	End If
10	End Sub

3.セルをクリックするとドロップダウンリストが表示される

サンプルVBAの2行目には「ドロップダウンリストの設定をリセットする」というプログラムを記載しています。これがなければエラーが表示されてしまうので記載忘れがないよう注意してください。

サンプルVBAの5行目には「入力した値は正しくありません。という内容のエラーを表示させない」というプログラムを記載しています。5行目を削除すると下図のようなエラーが表示されてリスト以外のテキストを入力することができなくなります。

ひらめく人

私はいつもエラーを表示させない処置を行っています。データの運用の仕方によってエラーを表示させるかさせないかを使い分けましょう。

サンプル事例② 指定されたセルをクリックすると簡単なリストが表示される

サンプル事例②は以下の内容になります。

A5セルをクリックするとリストが表示される
VBAに直接リスト項目を入力する（正解、不正解、三角）
A5セルにリスト項目以外のテキストを入力するとエラーを表示する

上記の条件でサンプルVBAを作成していきます。

1.VBAを記入する場所を選択

2.サンプルVBAをコピペする

1	Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
2	Range(“A5").Validation.Delete
3	If (Target.Row = 5 And Target.Column = 1) Then
4	With Target.Validation
5	.Add Type:=xlValidateList, Formula1:="正解,不正解,三角"
6	End With
7	Else
8	End If
9	End Sub

3.A5セルをクリックするとドロップダウンリストが表示される

Targetを使ってセルを指定するときTarget.Range()やTarget.Cell()にしてしまいがですが、これではVBAを動かすことはできません。

Targetを使う場合はRowとColumnを使わなければなりません。サンプルVBAの3行目では「Target.Row = 5 And Target.Column = 1」というプログラミングにしてA5セルを指定しています。

ひらめく人

引数Targetは「セルをクリックしてVBAを実行させる」ときによく使われるプログラムなので使い方をマスターしておきましょう。

サンプル事例③ 一覧表（縦方向）からリストを表示させる

サンプル事例③は以下の内容になります。

A5セルをクリックするとドロップダウンリストが表示される
E列に作成された一覧表の情報を取得しドロップダウンリストに反映させる
一覧表の項目が増減したときリスト項目も自動的に増減される

上記の条件でサンプルVBAを作成していきます。

1.VBAを記入する場所を選択

2.サンプルVBAをコピペする

1	Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
2	rm = Cells(Rows.Count, 5).End(xlUp).Row
3	Dim risuto As Variant
4	risuto = WorksheetFunction.Transpose(Range(Cells(3, 5), Cells(rm, 5)))
5	Range(“A5").Validation.Delete
6	If (Target.Row = 5 And Target.Column = 1) Then
7	With Target.Validation
8	.Add Type:=xlValidateList, Formula1:=Join(risuto, “,")
9	End With
10	Else
11	End If
12	End Sub

3.リストに追加した項目が自動的にドロップダウンリストに反映される

リストの情報を取得するプログラムではCount関数やTranspose関数が使われています。Count関数はリストの一番下の項目の場所を取得し、Transpose関数ではリストの範囲を変数に格納する目的で使用しています。

下記の記事では、Count関数の使い方を詳しくご紹介しています。

mamemametochan.com

VBAで行数をカウントする方法【エクセル業務の効率をアップさせるCount関数の使い方】

https://mamemametochan.com/excel-3

疑問がある人一覧表の行数が変わったときに自動的に行数を調べる方法ってないの？こんな疑問にお答えします。結論からいうと、VBAでカウント関数を設定すれば、一覧表の行数が変わっても自動的に対処することが可能です。 VBAでカウント関数を上手に扱うことができれば、エクセル業務の効率を格段にアップさせることができます。カウント関数でどんなことができるのか、実際の仕事にどのようにカウント関数を導入することができるのか、をサンプル事例を使って詳しくご紹介します。 VBAを使って行数をカウントすると何ができ...

ガッツポーズの人

Count関数やTranspose関数が難しくて理解できない場合、サンプルVBAをコピペしてリスト範囲のプログラムだけ編集して使ってみてください。このVBAは実務でかなり使えます。

サンプル事例④ 一覧表（横方向）からリストを表示させる

サンプル事例④は以下の内容になります。

A5セルをクリックするとドロップダウンリストが表示される
2行目に作成された一覧表の情報を取得しドロップダウンリストに反映させる
一覧表の項目が増減したときリスト項目も自動的に増減される

サンプル事例③とほとんど内容は変わりませんが、一覧表から情報を取得する方向が異なります。具体的には下図のようなイメージです。

情報を取得する向きが変わるだけなら簡単！と思っていたのですが、意外と難しい内容だったためサンプル事例として紹介しています。

1.VBAを記入する場所を選択

2.サンプルVBAをコピペする

1	Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
2	retu = Cells(2, Columns.Count).End(xlToLeft).Column
3	Dim risuto As Variant
4	risuto = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range(Cells(2, 2), Cells(2, retu))))
5	Range(“A5").Validation.Delete
6	If (Target.Row = 5 And Target.Column = 1) Then
7	With Target.Validation
8	.Add Type:=xlValidateList, Formula1:=Join(risuto, “,")
9	End With
10	Else
11	End If
12	End Sub

3.リストに追加した項目が自動的にドロップダウンリストに反映される

サンプル事例③とサンプル事例④の違いはリストの情報を取得する向きを縦方向から横方向に変更したことです。サンプルVBAでいうと4行目のプラグラムが大きく異なっています。

Transpose関数の中にTranspose関数を入れることで情報を取得する向きを変えています。

ひらめく人

縦方向と横方向の情報を取得できれば、どんな様式の一覧表からでも情報を取得することができるようになります。

サンプル事例⑤ 条件によってリスト内容を変化させる

サンプル事例⑤は以下の内容になります。

A列に「果物、野菜」という項目のドロップダウンリストを表示させる
A列に果物と入力された場合、B列のドロップダウンリストには「りんご、みかん」という項目を表示させる
A列に野菜と入力された場合、B列のドロップダウンリストには「白菜、玉ねぎ」という項目を表示させる

1.VBAを記入する場所を選択

2.サンプルVBAをコピペする

1	Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
2	rm = Cells(Rows.Count, 1).End(xlUp).Row
3	Range(“A:A").Validation.Delete
4	Range(“B:B").Validation.Delete
5	If Target.Column = 1 And Target.Row <> 1 Then
6	With Target.Validation
7	.Add Type:=xlValidateList, Formula1:="果物,野菜"
8	End With
9	Else
10	End If
11	For i = 2 To rm
12	On Error Resume Next
13	Dim VA As String
14	VA = Cells(Target.Row, Target.Column – 1)
15	K = “果物"
16	T = “野菜"
17	If (Target.Column = 2) And (VA = K) Then
18	With Target.Validation
19	.Add Type:=xlValidateList, Formula1:="リンゴ,みかん"
20	.ShowError = False
21	End With
22	ElseIf (Target.Column = 2) And (VA = T) Then
23	With Target.Validation
24	.Add Type:=xlValidateList, Formula1:="白菜,玉ねぎ"
25	.ShowError = False
26	End With
27	End If
28	Next
29	If Err <> 0 Then
30	Err.Clear
31	End If
32	End Sub