VBAでドロップダウンリストを設定する方法【コピペ可能サンプルを掲載】

 

 

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

 

結論から言うと、

VBAを使えばリスト内容が自動的で変化するドロップダウンリストを設定できる！

です。

 

VBAを使わずにエクセルのツールバーの機能からドロップダウンリストを設定するだけでも手入力作業の省力化につながりますが、

VBAを使ってドロップダウンリストを設定することで

リストから項目を選択する行為の省力化を追加することができます。

 

表現が回りくどくてわかりにくいと思いますが、

記事で紹介しているサンプルVBAを見ると納得して頂けるはずです。

 

この記事を参考にすることで得られる効果がこちら

紹介されている「VBAでドロップダウンリストを設定する方法」をマスターすれば

いろんな業務に応用することが可能になります。

 

ドロップダウンリストを取り入れてエクセル作業の効率をアップさせたい！

と考えている方は是非参考にしてみてください。

 

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

 

そもそもドロップダウンリストとは

セルに直接テキストを入力するのではなく、あらかじめ設定したリストを呼び出しリストの中から項目を選択しセルに入力できる

というプログラムのことです。

 

 

具体的には下の図のような処理が可能になります。

 

ドロップダウンリストが設定されたセルをクリックするとあらかじめ設定しておきたリストが表示される。

今回のリストは「〇」と「×」の２つのテキストだけです。

 

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

 

同じ入力内容の繰り返し！ある程度決まっている用語しか入力することがない！

という作業にドロップダウンリストの設定が有効です。

 

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

 

VBAを使ってドロップダウンリストを設定するには

validation.addメソッドと引数Targetを利用します。

 

この２つのVBAを組み合わせることで

セルをクリックしてドロップダウンリストを表示させる

という処理が可能に。

 

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

 

validation.addメソッドとは

 

validation.addメソッドとは、

指定した範囲にデータの入力規則を追加する

というVBAです。

 

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

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

 

参考書やネットで「VBAを使ったドロップダウンリストの設定方法」と調べると

上記のような構文やパラメータの解説記事がほとんど。。

 

構文やパラメータについて一生懸命勉強するよりも、

いくつかのサンプルを見比べた方がValidation.Add メソッドについて理解できると思います。

 

記事の中では、５つのサンプル事例を紹介しています。

 

サンプル事例と上記の構文・パラメータを見比べて

Validation.Add メソッドのイメージを膨らめせてみてください。

 

引数Targetとは

参考書やネットを使って引数Targetの定義を調べてみたのですが、

明確なことはよくわかりませんでした。

 

しかし、完璧に定義を理解できなくても

引数Targetを使いこなすことは十分可能です。

 

今回は、

指定するセル(ターゲットしたセル)をクリックするとドロップダウンリストを表示させる

という使い方を紹介しています。

 

引数Targetを簡単に説明すると、

指定した範囲でなんらかのイベントが発生したときに、なんらかの処理をする

というVBAです。

 

 

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

 

 

「ドロップダウンリストを表示させる」以外にもいろんな処理に応用させることができます。

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

 

 

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

いくつかのサンプル事例を見比べることで

ドロップダウンリストの設定に関するVBAの理解を深めることができます。

 

大きく分けて２種類のサンプル事例を紹介しています。

• 決められたリスト内容を表示させる
• 条件によってリスト内容を変化させる

 

５つ全てのサンプルに

Validation.Add メソッドと引数Targetが利用されています。

 

 

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

 

サンプル事例①の内容がこちら

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

 

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

 

 

2.サンプルVBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Cells.Validation.Delete
With Target.Validation
.Add Type:=xlValidateList, Formula1:="〇,×"
.ShowError = False
End With
If Err <> 0 Then
Err.Clear
End If
End Sub

 

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

 

サンプルVBAの2行目が

ドロップダウンリストの設定をリセットする

 

サンプルVBAの5行目が

「入力した値は正しくない」というエラーを表示させない

というプログラムになっています。

 

５行目のプログラムを記載しなければ下図のようなエラーが表示され、

リスト以外のテキストを入力することができなくなります。

 

 

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

サンプル事例②の内容がこちら

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

 

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

 

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

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Range("A5").Validation.Delete
If (Target.Row = 5 And Target.Column = 1) Then
With Target.Validation
.Add Type:=xlValidateList, Formula1:="正解,不正解,三角"
End With
Else
End If
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を実行させる」ときによく使われるプログラム

なので使い方をマスターしておきましょう。

 

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

サンプル事例③の内容はこちら

 

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

 

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

 

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

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

 

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

 

リストの情報を取得するプログラムでは

Count関数やTranspose関数が使われています。

 

Count関数はリストの一番下の項目の場所を取得し、

Transpose関数ではリストの範囲を変数に格納する目的で使用しています。

 

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

 

 

 

Transpose関数を始めて見た！という方は

イメージしにくく難しいと感じるかと思います。

 

一覧表を参照するVBAは実務でかなり使えるものなので、

使い慣れるまでサンプルVBAをコピペして使ってみてください。

 

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

サンプル事例④の内容はこちら

サンプル事例③とほとんど内容は変わりませんが、

一覧表から情報を取得する方向が異なります。

 

具体的には下図のようなイメージです。

 

情報を取得する向きが変わるだけなら簡単！と思っていたのですが、

意外と難しい内容だったためサンプル事例として紹介しています。

 

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

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

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

 

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

 

サンプル事例③とサンプル事例④の違いは

リストの情報を取得する向きを縦方向から横方向に変更したことです。

 

サンプルVBAでいうと4行目のプラグラムが大きく異なっていて、

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

 

 

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

サンプル事例⑤の内容はこちら

 

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

 

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

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
rm = Cells(Rows.Count, 1).End(xlUp).Row
Range("A:A").Validation.Delete
Range("B:B").Validation.Delete
If Target.Column = 1 And Target.Row <> 1 Then
With Target.Validation
.Add Type:=xlValidateList, Formula1:="果物,野菜"
End With
Else
End If
For i = 2 To rm
On Error Resume Next
Dim VA As String
VA = Cells(Target.Row, Target.Column - 1)
K = "果物"
T = "野菜"
If (Target.Column = 2) And (VA = K) Then
With Target.Validation
.Add Type:=xlValidateList, Formula1:="リンゴ,みかん"
.ShowError = False
End With
ElseIf (Target.Column = 2) And (VA = T) Then
With Target.Validation
.Add Type:=xlValidateList, Formula1:="白菜,玉ねぎ"
.ShowError = False
End With
End If
Next
If Err <> 0 Then
Err.Clear
End If
End Sub

 

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

 

条件によってリスト内容を変化させるVBAは

いろんな関数が使われていてとても複雑ですが、

導入することができれば

リスト項目が増えすぎてドロップダウンリストが扱いにくくなる！

というデメリットを解消することができます。

 

リスト項目の見直しをすることがあれば

「条件によってリスト内容を変化させるVBA」の導入にチャレンジしてみてください。

 

職場ではかなり昔に設定されたドロップダウンリストが使えなくなってきたので、

設定を見直すことに。。

おもいきって「条件によってリスト内容を変化させるVBA」を導入してみました。

 

結果、仕事の効率が２倍以上アップ。

エクセルが得意ではない年配の方でも簡単に資料を編集することができるようになりました。

 

下記のサイトでは、

条件によってリスト内容が変化するVBAが設定されたエクセル家計簿

をダウンロードしてお使いいただくことが可能です。

 

 

「条件によってリスト内容を変化させるVBA」が設定されているので、

家計簿の入力作業がとても簡単。

 

ドロップダウンリストに表示される項目が常に最小限なので、

めちゃくちゃ入力しやすいと好評です。

 

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

 

マメBlogではエクセルマクロの開発依頼を受注しており、

「VBAを使ったドロップダウンリストの設定」の依頼も承っております。

 

「条件によってリスト内容が変化するVBA」を設定したいけど難しい！

という方におすすめです。

 

「このようなVBAを設定してほしい。」「この様式にVBAを設定してほしい。」

といった簡単な内容でOKです。

 

ご要望にお応えするVBAを提案、開発させていただきます。

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