Excel VBA ドロップダウンリストの作成と適用

目的

Excelにはドロップダウンリストを作成することでセル内に入力できる文字を限定することができる。
今回は、任意の文字列をリストして、そこから選択して入力できるようにする処理をVBAを用いて作成する。

使用するセル

　今回は下のようなシートを使用した。
　B2~B4の入力を、武器一覧の「木の棒」、「さびた刀」、「サーベル」のいずれかに制限できるようにする。

	A	B	C	D	E
1	名前	武器名			武器一覧
2	A				木の棒
3	B				さびた刀
4	C				サーベル

(シート名「Sheet1」)

プログラム

Private Sub ドロップダウンリストからの入力()

    '武器リストの作成
    Dim weopons() As Variant
    weapons = WorksheetFunction.Transpose _
    (Worksheets("Sheet1").Range("E2:E4").Value)
    
    'B2～B4に武器リストの要素のみ入力できるようにする
    For Each myRange In Worksheets("Sheet1").Range("B2:B4")
        
        '既に設定されている入力規則を削除する
        myRange.Validation.Delete
        
        '先ほど作成したリストを入力規則とする
        myRange.Validation. _
        Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:=Join(weapons, ",")
    Next
    
End Sub

プログラムの解説

その1(リストの作成)

'武器リストの作成
    Dim weopons() As Variant
    weapons = WorksheetFunction.Transpose _
    (Worksheets("Sheet1").Range("E2:E4").Value)

概要

2次元配列のセルE2~E4を一次元配列に変換し、それをVariant型の一次元配列に格納

詳細

縦に並んだセルは二次元配列にしか格納できないが、
WorksheetFunction.Transpose(セル範囲)
により一次元配列に格納できるようになるらしい。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.transpose
によるとWorksheetFunction.Transposeをすると、セルの行と列を入れ替えることができるらしい。

2次元配列を行列で考えるのならば、行列を転置することと同義であると考えられる。

つまり、今回の例では二次元配列を転置することにより、
　　　　2行1列の行列→1行2列の行列
に変換しているとみなせる(2次元配列をn行m列の行列と考えると、一次元配列は1行m列の行列である)。

また、weapons()をvariant型としているのは、Transposeの返り値がvariantのため。

その2(セルにリストを適用)

For Each myrange In Worksheets("Sheet1").Range("B2:B4")
        myrange.Validation. _
        Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:=Join(weapons, ",")
Next

概要

セルB2～B4にリストを適用する。

詳細

Validation. _
Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=Join(weapons, ",")
により、リストの適用方法を決定している。

Type:=xlValidateListによりリストからの入力を適用できるようにている。
更に、Formula1:=Join(weapons, ",")により、先ほど作成したリストを適用しているようにする。

また、AlertStyleにはセルにリスト以外の文字列を入力した場合の警告の方法について指定できる。
xlValidAlertWarningやxlValidAlertInformationでは、既定値以外を入力した場合にポップアップが表示されるが、そのまま確定することが可能なので、今回は既定値以外の入力を受け付けないxlValidAlertStopとした。

まとめ

今回はセル内にリストで指定した文字しか入力できないようにしましたが、「1~255」までの数値などの指定方法もあるみたいです。
プログラムの解説が見にくくなってしまったので、次回以降は改善していきたいです(・ω・)。