【VBA】ドロップダウンリストに絞り込み機能を付ける方法を解説

 

ドロップダウンリストに絞り込み機能を付けて使いやすくしたい！

こんな要望にお応えします。

 

結論からいうと、

関数IFとドロップダウンリストを組み合わせてリスト項目を変化させる条件を付与する

です。

 

表示されるリスト項目が10個、20個になった場合、選びづらくて使いにくい！と感じるようになるはずです。

 

こんな感じ

 

絞り込み機能付きのドロップダウンリストを設定すればこんな問題を解決することが可能です。

仕事でよくドロップダウンリストを設定している！という方は是非参考にしてみてください。

 

絞り込み機能付きドロップダウンリストとは

 

 

絞り込み機能付きドロップダウンリストとは、

条件によって表示されるリスト項目が変化(厳選)される

というものです。

 

どのような処理をするのかというとこんな感じ

絞り込み機能があるおかげで3個～5個くらいの項目が表示されています。

もし絞り込み機能を設定せずにリストを表示させた場合、常に10個以上の項目が表示されることに。。

 

「日用品」と入力した場合、「日用品に関連する項目だけ」表示されてほしい

絞り込み機能付きドロップダウンリストはこんな要望を叶えるマクロになっています。

 

仕事でめちゃくちゃ応用できるマクロなので是非この記事を確認して使い方をマスターしてください。

 

絞り込み機能付きドロップダウンリストの設定方法

仕事でよく応用される2種類の「絞り込み機能付きドロップダウンリスト」の設定方法をご紹介します。

 

サンプルマクロ　テキスト内容によってリスト項目を変化させるマクロ

テキスト内容によってリスト項目を変化させるマクロは以下のような動きをします。

 

仕事では、

「〇〇会社名」を入力すると「〇〇会社に所属する人」が表示され、「××会社名」を入力すると「××会社に所属する人」が表示される

という使い方ができます。

 

マクロの導入方法

様式の設定

 

VBAの設定画面を開く

 

Sheetにコードを入力する

 

これでマクロの導入が完了です。

A列、ｂ列にドロップダウンリストが表示されるので試してみてください。

 

サンプルマクロ

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
'1列目の最終行数を取得し変数rmに格納する
rm = Cells(Rows.Count, 1).End(xlUp).Row
'A列とB列のドロップダウンリストを削除
Range("A:A").Validation.Delete
Range("B:B").Validation.Delete
'変数Kに「果物」を格納する
If Target.Column = 1 And Target.Row <> 1 Then
'「果物」「野菜」をリスト化する
With Target.Validation
.Add Type:=xlValidateList, Formula1:="果物,野菜"
End With
Else
End If
'2行目から変数rmまでを繰り返す
For i = 2 To rm
On Error Resume Next
Dim VA As String
'クリックしたセルの1つ左のセルの情報を変数VAに格納する
VA = Cells(Target.Row, Target.Column - 1)
'変数Kに「果物」を格納する
K = "果物"
'変数Tに「野菜」を格納する
T = "野菜"
'もし2列目をクリックし、かつ変数VAが変数Kだったら
If (Target.Column = 2) And (VA = K) Then
'ドロップダウンリストに「リンゴ」「みかん」を表示する
With Target.Validation
.Add Type:=xlValidateList, Formula1:="リンゴ,みかん"
.ShowError = False
End With
'もし2列目をクリックし、かつ変数VAが変数Tだったら
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

 

サンプルマクロ　別シートの一覧表情報をリスト化するマクロ

別シートの一覧表情報をリスト化するマクロは以下のような動きをします。

 

仕事では、

「設定」Sheetを設けて会社名、氏名、住所、連絡先の情報をまとめる。まとめた情報を別シートにドロップダウンリストで表示する

という使い方ができます。

 

マクロの導入方法

様式の設定

 

 

VBAの設定画面を開く

 

Sheetにコードを入力する

 

これでマクロの導入が完了です。

A5セルにドロップダウンリストが表示されるので試してみてください。

 

サンプルマクロ

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Dim Ash As Worksheet
Set Ash = Sheets("サンプル事例②")
'５列目の最終行数を取得し変数rmに格納する
rm = Ash.Cells(Rows.Count, 5).End(xlUp).Row
'変数ristoを宣言
Dim risuto As Variant
'Transpose関数を使って変数risutoに配列化した一覧表のテキストを格納
risuto = WorksheetFunction.Transpose(Ash.Range(Ash.Cells(3, 5), Ash.Cells(rm, 5)))
'A5セルのドロップダウンリストを削除
Range("A5").Validation.Delete
'もし5行目かつ1列目(A5セル)をクリックしたとき
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

 

VBA未経験者が絞り込み機能付きドロップダウンリストマクロを導入する方法

 

絞り込み機能付きドロップダウンリストマクロはエクセル業務でかなり使えます。

ですが、誰でも簡単にマクロを導入できるわけではなりません。

VBA未経験者の場合、この記事で紹介してきた2種類のマクロの導入方法を参考にしても業務内容に合わせたマクロにアレンジするのは難しいと感じる方が多いのではないでしょうか。

 

そんなVBA未経験者の方でも業務内容に合わせた絞り込み機能付きドロップダウンリストマクロを導入する方法をご紹介します。

 

専門家にマクロ開発依頼をする

VBA未経験者であっても、専門家に依頼することで業務内容に合わせたマクロを導入することができます。

 

依頼方法としては、

「こんなような仕事をしていて、こんなような作業で困っている」と業務内容、問題点を報告。そして、「こんなような処理を自動化したい」という要望事項や改善事項を相談してVBAの開発を依頼する

です。

 

開発費用はかかってしまいますが、通常業務をこなしつつ仕事の効率を2倍、3倍にアップさせることができるマクロを導入することができるのでめちゃくちゃ効率的。

エクセル業務のウェイトが多くて困っているという方は「マクロの開発依頼をする」方法がめちゃくちゃおすすめです。

 

当マメBlogでも業務内容に合わせたVBA開発依頼を受注しています。

 

 

マメBlogが行うVBA開発の特徴がこちら

 

土曜日、日曜日、祝日関係なしに作業させていただきますので「スピード納入」が可能です。

 

まずは「どんな処理を自動化したいのか」をご相談ください。

「VBA開発が可能か」「どのようなVBAを設定すべきか」「見積金額はどれくらいなのか」を打合せした後、作業を開始いたします。

 

 

 

 

絞り込み機能付きドロップダウンリストが設定されたエクセルファイルをダウンロードする

VBAの開発依頼よりも安価で手軽にマクロを導入する方法は、既にマクロが設定されたエクセルファイルをダウンロードする！です。

業務内容にピッタリのマクロが設定されたエクセルファイルを見つけることはかなり難しいですが、ダウンロードすることができればすぐに効率化マクロを導入することが可能です。

 

下記の記事では、この記事で紹介している絞り込み機能付きドロップダウンリストマクロが設定されたエクセルファイルをダウンロードしてお使い頂くことができます。
 

 
ダウンロードしたエクセルファイルをそのまま使うもよし！設定されているコードをコピペして使うもよし！

好きにお使い頂くことができます。

 

サイトでは絞り込み機能付きドロップダウンリストマクロの他にもエクセル業務の効率をアップさせるマクロがいくつもご紹介されていますので是非参考にしてみてください。

 

 

絞り込み機能付きドロップダウンリストマクロを導入することでエクセル業務の効率がアップ

 

職場のエクセルファイルには「データの入力規則」で設定する一般的なドロップダウンリストが多数設定されていました。

これだけでも入力作業の効率はアップするのですが、長年使い回しているせいで不要な項目が多く表示されているという問題点があります。

 

絞り込み機能付きドロップダウンリストを導入することでこのような問題を解決することが可能に。

必要最低限の項目を表示する条件にすることで表示される項目が数十個から数個に減少させることができました。

 

エクセルの操作が苦手という年配の方でもスムーズに入力作業を行うことができるようになったので職場全体の作業効率アップに繋がったと感じています。