EXCEL VBA ドロップダウンリスト・プルダウンリスト・コンボボックスの作成（リスト選択）

2020年5月31日

EXCEL VBA ドロップダウンリスト・プルダウンリスト・コンボボックスの作成（リスト選択）

●はじめに

EXCELでデータを選択する際や同じデータを登録する時に利用するのが、ドロップダウンリスト（プルダウンリスト）です。通常のEXCELでドロップダウンリストを使用する場合は、リストの内容を変更する時は、リストを再設定する必要があります。そのドロップダウンリストをEXCEL VBAで利用すると、簡単にリストを再設定する事が可能です。それでは、サンプルプログラムを交えて説明します。

●書式の説明　　「ValidationAddメゾット」

●　Rangeオブジェクト.Add(Type,Alertstyle,Operator,Formula1,Formula2)

●『使用例』
Range(“A1″).Validation.Add Type:=xlValidateList, Formula1:=”上期,下期”
　Formula1:=”上期,下期” ‘　⇒　(リスト内容の設定)

パラメータ（引数）	必須・省略可能	説明
Type	必　　須	入力規則の種類を指定します。
Alertstyle	省略可能	入力規則でのエラースタイルを指定する事が出来ます。
Operator	省略可能	データ入力規則の演算子を指定します。
Formula1	省略可能	データ入力規則での条件式の最初の部分を指定します。値は 255 文字を超える事はできません。
Formula2	省略可能	引数_Operator_がXlbetweenまたはxlnotbetweenの場合、データ入力規則の2番目の部分を指定します。それ以外の場合、この引数は無視します。

●ValidationAddメゾットを使ってのドロップダウンリストの注意点
・VBAプログラムを利用してドロップダウンリストを作成する際に、１つ注意点があります。それは、一度ドロップダウンリストを作成したセルで、再度、ドロップダウンリストを作成すると、エラーが発生します。

【エラーを回避する方法】
・一度ドロップダウンリストを削除します。（作成したセル）
・再度、ドロップダウンリストを作成します。

ドロップダウンリストの作成（プルダウンリストの作成）

●プログラム説明　（サンプル①）　ValidationAddメゾット

下記のサンプルプログラムは、ドロップダウンリストを作成するサンプルプログラムです。
セルの「B３」と「D3」に山手線の駅名のドロップダウンリストを作成します。登録するリストデータは、プログラム内に記述（駅名）しております。

'
'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/
'

Sub Drop_down_list01() 'ドロップダウンリストの作成


    With Range("B3", "D3").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="新宿,代々木,原宿,渋谷,恵比寿,目黒,五反田,大崎,品川,高輪ゲートウェイ,田町,浜松町"
    End With


End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/

Sub Drop_down_list01() 'ドロップダウンリストの作成

With Range("B3", "D3").Validation

.Delete

.Add Type:=xlValidateList, Formula1:="新宿,代々木,原宿,渋谷,恵比寿,目黒,五反田,大崎,品川,高輪ゲートウェイ,田町,浜松町"

End With

End Sub

●実行前～実行後　※プログラム実行後、セルの「B３」・「D３」にドロップダウンリストが作成されました。
（画面クリックして拡大）

ドロップダウンリスト範囲を自動取得

●プログラム説明　（サンプル②）　ValidationAddメゾット

下記のサンプルプログラムは、サンプル①同様にドロップダウンリストを作成するサンプルプログラムですが、サンプル①は、プログラム内にドロップダウンリスト内容を記述しておりましたが、今回は、ワークシートのF列にリストを作成してプログラムを実行するたびにリスト内容を更新します。また、リストに登録されていない内容を入力するとエラーメッセージが表示する様に設定しています。

'
'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/
'
Sub Drop_down_list02() 'ドロップダウンリスト範囲を自動取得（リスト内容の更新）

    
    Dim lRow As Long

    lRow = Cells(Rows.Count, "F").End(xlUp).Row  'F列の最終行を取得


    With Range("B3", "D3").Validation
        .Delete  'ドロップダウンリストを削除
        .Add Type:=xlValidateList, Formula1:="=$F$4:$F$" & lRow  'リスト範囲を登録（F列データの最終行まで）
        .ErrorMessage = "リストに登録されていません。"  'エラーメッセージを登録
    End With


End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/

Sub Drop_down_list02() 'ドロップダウンリスト範囲を自動取得（リスト内容の更新）

Dim lRow As Long

lRow = Cells(Rows.Count, "F").End(xlUp).Row 'F列の最終行を取得

With Range("B3", "D3").Validation

.Delete 'ドロップダウンリストを削除

.Add Type:=xlValidateList, Formula1:="=$F$4:$F$" & lRow 'リスト範囲を登録（F列データの最終行まで）

.ErrorMessage = "リストに登録されていません。" 'エラーメッセージを登録

End With

End Sub

●実行前～実行後　※プログラム実行後、F列に設定してあるドロップダウンリスト内容を元にリスト内容が更新されました。
（画面クリックして拡大）

ドロップダウンリストの範囲を別のシートから取得（別シート参照）

●プログラム説明　（サンプル③）　ValidationAddメゾット

下記のサンプルプログラムは、ドロップダウンリストの範囲を別シートから取得するサンプルプログラムです。２つのワークシート「小口現金出納帳」・「項目マスター」を作成して、「小口現金出納帳」にデータ入力用のドロップダウンリストを作成し、「項目マスター」には、ドロップダウンリストの元となるデータ（勘定科目）・（補助科目）・（消費税区分）を登録してリスト管理します。

●下記のサンプルプログラムを実行する際は、下記の通りにシート名を設定して下さい。
※　シート名：「小口現金出納帳」　　ドロップダウンリストを作成します。
※　シート名：「項目マスター」　　　ドロップダウンリストのデータ参照に利用します。

'
'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/
'
Sub Drop_down_list03() 'ドロップダウンリスト範囲を別のシートから取得（別シート参照）

    Dim ws01, ws02 As Worksheet
    Dim lRow, mRow As Long

    
    Set ws01 = Worksheets("小口現金出納帳")
    Set ws02 = Worksheets("項目マスター")

    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row  'ワークシート「小口現金出納帳」のA列の最終行を取得
    
    
    '勘定科目の設定
    mRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row  'ワークシート「項目マスター」のB列(勘定科目)の最終行を取得

    With ws01.Range("C5：C" & lRow).Validation
        .Delete  'ドロップダウンリストを削除
        .Add Type:=xlValidateList, Formula1:="=項目マスター!$B$4:$B$" & mRow  'リスト範囲を登録（B列データの最終行まで）
    End With

    '補助科目の設定
    mRow = ws02.Cells(Rows.Count, "C").End(xlUp).Row  'ワークシート「項目マスター」のC列(補助科目)の最終行を取得

    With ws01.Range("D5：D" & lRow).Validation
        .Delete  'ドロップダウンリストを削除
        .Add Type:=xlValidateList, Formula1:="=項目マスター!$C$4:$C$" & mRow  'リスト範囲を登録（C列データの最終行まで）
    End With
    
    '消費税の設定
    mRow = ws02.Cells(Rows.Count, "D").End(xlUp).Row  'ワークシート「項目マスター」のD列(消費税区分)の最終行を取得

    With ws01.Range("G5：G" & lRow).Validation
        .Delete  'ドロップダウンリストを削除
        .Add Type:=xlValidateList, Formula1:="=項目マスター!$D$4:$D$" & mRow  'リスト範囲を登録（D列データの最終行まで）
    End With

End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/Drop_down_list/

Sub Drop_down_list03() 'ドロップダウンリスト範囲を別のシートから取得（別シート参照）

Dim ws01, ws02 As Worksheet

Dim lRow, mRow As Long

Set ws01 = Worksheets("小口現金出納帳")

Set ws02 = Worksheets("項目マスター")

lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'ワークシート「小口現金出納帳」のA列の最終行を取得

'勘定科目の設定

mRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row 'ワークシート「項目マスター」のB列(勘定科目)の最終行を取得

With ws01.Range("C5：C" & lRow).Validation

.Delete 'ドロップダウンリストを削除

.Add Type:=xlValidateList, Formula1:="=項目マスター!$B$4:$B$" & mRow 'リスト範囲を登録（B列データの最終行まで）

End With

'補助科目の設定

mRow = ws02.Cells(Rows.Count, "C").End(xlUp).Row 'ワークシート「項目マスター」のC列(補助科目)の最終行を取得

With ws01.Range("D5：D" & lRow).Validation

.Delete 'ドロップダウンリストを削除

.Add Type:=xlValidateList, Formula1:="=項目マスター!$C$4:$C$" & mRow 'リスト範囲を登録（C列データの最終行まで）

End With

'消費税の設定

mRow = ws02.Cells(Rows.Count, "D").End(xlUp).Row 'ワークシート「項目マスター」のD列(消費税区分)の最終行を取得

With ws01.Range("G5：G" & lRow).Validation

.Delete 'ドロップダウンリストを削除

.Add Type:=xlValidateList, Formula1:="=項目マスター!$D$4:$D$" & mRow 'リスト範囲を登録（D列データの最終行まで）

End With

End Sub

●実行前～実行後　※プログラム実行後、ワークシート「小口現金出納帳」の項目（勘定科目）・（補助科目）・（消費税区分）にドロップダウンリストが作成されました。ドロップダウンリストの元データは、ワークシート「項目マスター」から取得しています。
なお、「項目マスター」を追加・修正後にプログラムを実行しますと、ドロップダウンリストの内容も追加・修正されます。
（画面クリックして拡大）