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

Left Caption

疑問がある人

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

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

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

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

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

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

 

 

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

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

 

Right Caption

困った人

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

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

 

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

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

 

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を使ったドロップダウンリストの設定方法」を調べると上記のように、構文やパラメータの解説記事がほとんどだと思います。

 

Right Caption

ダウンする人

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

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

 

引数Targetとは

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

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

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

 

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

 

 

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

 

 

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

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

 

Left Caption

ひらめく人

基本的には上記で解説した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行目を削除すると下図のようなエラーが表示されてリスト以外のテキストを入力することができなくなります。

Left Caption

ひらめく人

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

 

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

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

 

  • 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セルを指定しています。

 

Left Caption

ひらめく人

引数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関数の使い方を詳しくご紹介しています。

 

Left Caption

ガッツポーズの人

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関数を入れることで情報を取得する向きを変えています。

 

Left Caption

ひらめく人

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

 

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

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

 

  • 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

 

3.A列に入力された項目内容によってB列のリスト内容が変化する

条件によってリスト内容を変化させるVBAはいろんな関数が使われていてとても複雑です。しかしこのVBAを導入すれば、リスト項目が増えすぎてせっかく設定したドロップダウンリストが扱いにくくなる!というデメリットを解消することができます。

リスト項目の見直しをすることがあれば「条件によってリスト内容を変化させるVBA」の導入にチャレンジしてみてください。

 

Left Caption

上を目指す人

条件によってリスト内容を変化させるVBAはエクセル業務の効率アップに繋がるのでかなりおすすめです。

下記の記事では、条件によってリスト内容が変化するVBAが設定されたエクセル家計簿についてご紹介しています。

入力されているテキストが収入の場合リスト内容には給料やボーナスといった収入関係の項目が表示され、入力されているテキストが支出の場合リスト内容には食費や生活費といった支出関係の項目が表示される設定にしています。

ドロップダウンリストに表示される項目が常に最小限なので、めちゃくちゃ入力しやすいと好評です。

 

VBAを使ったドロップダウンリストの設定は外注依頼がおすすめ

マメBlogではエクセルマクロの開発依頼を受注しており、ドロップダウンリストの設定依頼を承っております。

上記で紹介している「条件によってリスト内容が変化するVBA」を使いたいけど自分で設定するには難しい!というお声をよく頂きます。こんなときはマメBlogにご相談ください。

VBAを設定したいエクセルファイルをメールで送信していただければ、ご要望にお応えするドロップダウンリストを設定させていただきます。

 

ご依頼事例

 

設定Sheetにある一覧表の情報を別Sheetにドロップダウンリストとして表示させたい。一覧表の項目は追加したり削除したりするので自動的にリスト項目も増減するVBAにしてほしい。

 

こんな感じでVBAの設定依頼をしていただいた事例があります。事例ではとてもしっかりした内容での依頼でしたが、もっとざっくりとした内容での依頼でも構いません。私の方から便利になる使い方を提案をさせていただきます。

VBAを設定するだけでエクセル作業の効率を格段にアップさせることができます。まずはお気軽にマメBlogにご相談ください。全力でVBAの開発に取り組ませていただきます。

<<エクセルVBAの開発依頼・ご相談はこちら