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初心者が効率化マクロを導入する方法

 

VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！

このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。

 

こんな問題を解決する方法がこちら

• マクロの開発を外注に依頼する
• マクロが設定されたエクセルファイルをダウンロードする
• 参考書やネットでVBAスキルを身に付ける

 

VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。

 

 

 

 

VBAは

他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

という特徴があります。

 

毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。

 

マクロの開発を外注に依頼する

マクロの開発を外注に依頼する導入方法は

VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法

です。

 

「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」

こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。

 

 

 

デメリットは、費用がかかることだけ。

開発内容、依頼先によってかかる費用は大きく異なります。

私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。

 

 

ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。

開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、

費用が安い！スピード納期！！

でやらせてもらっています。

 

ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。

 

ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。

 

 

マクロが設定されたエクセルファイルをダウンロードする

VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。

 

たとえば、

A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、

『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。

 

実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら

 

 

 

 

『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、

VBA初心者でもマクロを導入できる、費用が安い、

というメリットがあります。

 

しかし

VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい

というデメリットもあります。

 

 

個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。

 

今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。

 

 

 

参考書やネットでVBAスキルを身に付ける

先程も紹介しましたが、

VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

プログラミングです。

 

言い換えると、

情報量が豊富なので自分で勉強できちゃう

ということです。

 

私のVBAスキルはほとんど独学で身に付けたもので、

考えた処理のほとんどをVBAで表現できるレベルにまで到達した！

と思っています。

 

私の作ったマクロをプロのプログラマーが見れば、

お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！

と感じるはずです。

 

でも、

私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？

 

独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、

業務内容に合ったマクロを開発できるようになれます。

 

独学でVBAスキルを身に付けるコツは、

エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる

です。

 

とは言っても、全くの知識0の方は何をすればいいかわからないですよね。

 

VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。

 

VBAの知識0の方でもすいすい読み進められる内容になっています。

やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。

 

実際に私はこの参考書を読んでからVBAの勉強を始めました。

 

 

 

VBAの魅力を発見した後は、

ひたすら自動化したい処理を実現するコードを書くだけ

です。

 

【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。

 

実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると

自然といろんなマクロが作れるようになっているはずです。

 

VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。