Excel VBAでプルダウン（ドロップダウンリスト）を複数作成する方法

Excelのプルダウン（ドロップダウンリスト）を作成ことは多いかと思います。

久しぶりに作ろうとしても、操作方法を忘れたりして、慌てて調べるなんてこともあるかと思います。

そんな中で面倒だなと思うことは、

ではないでしょうか。

今回はそんなお悩みを解決する、
Excel VBAを使い、ボタンワンクリックで一瞬でプルダウン（ドロップダウンリスト）を複数個作成する方法
をご紹介します。

プルダウン（ドロップダウンリスト）のサンプルデータを準備する

プルダウン（ドロップダウンリスト）のサンプルデータを用意しましょう。

データの内容は何でもよいですが、

今回はサンプルで4つ用意しました。

1列目は国のリスト、
2列目は都市のリスト、
3列目は件数、
4列目は年代、

としています。

それをB列の8行目から入力しています。

なお、最大で255件までの登録となっていますので、255件以下にするようお願いします。

続いて、リストの上、

6行目に「タイトル」
7行目に「メッセージ」
を入力します。

2項目はプルダウンメニューをマウスオーバーした時に出てくる内容となります。

このような形になれば完成です。

VBAを実装する

続いてExcelのVisual Basic EditorへVBAを実装します。

Sub プルダウンを複数個作成()
   
  Dim list As Variant
  Dim n As Long
  
    '4列分のプルダウンを作成します
    For n = 2 To 5
      
      
            'プルダウンリストを取得します。最大100行まで取得します。
            list = Application.Transpose(Range(Cells(8, n), Cells(107, n)))
            
                '5行目のセルに挿入するプルダウンを設定します。
                With Cells(5, n).Validation
                
                    '既存のプルダウンがあれば削除します。
                    .Delete
                    
                    'リストを挿入します。
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(list, ",")
                    .IgnoreBlank = True
                    
                    'プルダウン形式を指定します。
                    .InCellDropdown = True
                    
                    'プルダウンのタイトルを指定します。
                    .InputTitle = Cells(6, n)
                    
                    'エラータイトルは空欄です。
                    .ErrorTitle = ""
                    
                    'マウスオーバー時のメッセージを指定します。
                    .InputMessage = Cells(7, n)
                    
                    'エラーメッセージは空欄です。
                    .ErrorMessage = ""
                    
                    .IMEMode = xlIMEModeNoControl
                    
                    .ShowInput = True
                    .ShowError = True
                
                End With

        
    Next
  
  MsgBox "5行目にプルダウンを作成しました", vbInformation
    
End Sub

実装手順は以下の通りです。

今回はExcel側にVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし

「プルダウンを複数個作成」を選択し、

「実行」をクリックします。

②「5行目にプルダウンを作成しました」が表示され、

プルダウンが5行目に追加されますね！

なお、作成したプルダウンは

コピペで別のシートや別のExcelへ移すことが可能です！

VBAを実行するボタンを追加する

マクロを実行するボタンを追加しておくと

ボタンぽちで一瞬で実行されるのでめちゃ便利です！

よく使う場合はぜひ実装しておきましょう。

実装の仕方は、

リボンメニュ「開発」＞「挿入」＞「フォームコントロール」をクリックし、

右上のアイコンをクリックします。

ボタンが挿入されるので、ボタンを右クリックし「マクロの登録」を選択します。

マクロの登録ウインドウが出たら、「プルダウンを複数個作成」を選択して、

「OK」をクリックし、完了です！

VBAの説明

VBAの内容を説明します。

今回は、4列分のプルダウンメニューを作成しますので、
4回繰り返しています。
5列にしてたい場合は5→6にしてください。

For n = 2 To 5

シートに記載されているプルダウンリストを取得します。
最大100行まで取得する設定にしています。
255個まで行けますので、100個では足りない場合は、Cells(107, n)を増やしてください。

list = Application.Transpose(Range(Cells(8, n), Cells(107, n)))

プルダウンを挿入する場所を指定します。
5行目のセルを指定しています。

With Cells(5, n).Validation

既存の入力規則であるプルダウンがあれば削除します。
.Delete

リストを挿入します。
入力値のタイプは「リスト」です。
.Add Type:=xlValidateList,

エラーメッセージのスタイルを「停止」に設定します。
AlertStyle:=xlValidAlertStop,

入力値の値の範囲を設定します。
Operator:=xlBetween,

条件式1はLISTのセル範囲を指定します。
Formula1:=Join(list, “,”)

セル範囲への空白値の入力を許可します。
.IgnoreBlank = True

プルダウン形式を指定します。
.InCellDropdown = True

プルダウンのタイトルを指定します。
.InputTitle = Cells(6, n)

エラータイトルは空欄です。
.ErrorTitle = “”

マウスオーバー時のメッセージを指定します。
.InputMessage = Cells(7, n)

エラーメッセージは空欄です。
.ErrorMessage = “”

IMEコントロールはコントロールなしを指定します。
.IMEMode = xlIMEModeNoControl

入力時メッセージが表示されます。
.ShowInput = True

無効なデータを入力すると、エラー メッセージが表示されるようにします。
.ShowError = True

さいごに

いかがでしょうか。

今回のVBAで効率よくプルダウンが作成、更新ができますので、

ぜひ活用いただければと思います。