- ListBox1.AddItem 変数で変数に代入された値をリストボックスに追加します
- ListBox1.List = Range(“名前”).Valueで定義された”名前”をリストに追加します
- ListBox1.RowSource = “名前”で定義された”名前”をリストに追加します
- 重複データを削除してリストボックスに追加するにはDictionaryオブジェクトを使用します
エクセルファイルの操作を間違えないよう誘導するのに、ユーザーフォームは非常に便利です。AddItemメソッド、Listプロパティ、RowSourceプロパティ、Dictionaryオブジェクト（重複削除）を使ってリストボックスにデータを追加する方法を紹介します。
コードのみで完結するリストボックス
追加するリストを全てコードの中で指定する方法です。ファイルを操作する人にリストを触られたくないケースや、一度作ってしまえばほぼリストの更新がないケースはこの方法がおすすめです。
AddItemメソッドでデータを追加
挿入→ユーザーフォームでフォームを作成し、表示→ツールボックスでリストボックスを選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。
フォームモジュールでUserForm_Initializeイベントを使用して、リストボックスを追加します。
Private Sub UserForm_Initialize()
Dim 守備位置 As Variant
Dim r As Variant
守備位置 = Array("内野手", "外野手", "投手", "捕手") '※1
For Each r In 守備位置
ListBox1.AddItem r '※2
Next r
ListBox1.ListIndex = 0 '※3
End Sub- 5行目…Array関数を使って、配列の要素としてテーブルを指定しています。
- 8行目…AddItemメソッドでリストを追加しています。
- 11行目…デフォルトのカーソル位置を指定しています。-1で非選択状態になります。
ワークシートのデータを利用するリストボックス
シートのデータを参照するのに名前の定義を使用します。OFFSET関数で参照範囲を可変させているので、データを更新（追加・削除）してもコードの変更は不要です。ただし、後述しますが初期化以降にデータを更新した場合、リストボックスに更新を反映するにはひと手間必要です。
Listプロパティでデータを追加
挿入→ユーザーフォームでフォームを作成し、表示→ツールボックスでリストボックスを選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。
リストボックスに追加するデータを定義します。A列1行目がフィールド名、以降をフィールドとして、参照範囲を=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)とします。ただし、データが空だとフィールド名を参照してしまいます。
リストボックスのデータは2次元配列です。前項のAddItemメソッドは空の配列に1件ずつデータを追加しますが、ワークシートのデータを利用すれば、Listプロパティでまとめて追加できます。
Private Sub UserForm_Initialize()
With ListBox1
.List = Range("リスト").Value '※1
.ListIndex = 0
End With
End Sub- 3行目…Range(“名前”).Valueで、定義した名前を配列として扱えます（Value省略不可）。
データが更新（追加/削除）されても即座にはリストボックスに反映されません。再イニシャライズ するか、一度データをクリア（Clearメソッド）して再登録する必要があります。
RowSourceプロパティでデータを追加
このパターンが一番おすすめです。このプロパティを使用すると、リストボックスのメソッドであるAddItem、Clear、RemoveItemが使用できなくなります（=コード上でデータの更新ができない）が、RowSourceプロパティと名前の定義を組み合わせる事で、シート上のデータ更新を即時リストボックスへ反映させる事ができます。
挿入→ユーザーフォームでフォームを作成し、表示→ツールボックスでリストボックスを選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。
リストボックスに追加するデータを定義します。A列1行目がフィールド名、以降をフィールドとして、参照範囲を=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)とします。ただし、データが空だとフィールド名を参照してしまいます。
Private Sub UserForm_Initialize()
With ListBox1
.RowSource = "リスト" '※1
.ListIndex = 0
End With
End Sub- 3行目…RowSourceプロパティは文字列で指定する必要があります。RangeオブジェクトのAddressプロパティを使用するならRowSource = Range(“リスト”).Addressとします。
重複データを削除したリストボックス
Dictionaryオブジェクトでデータを追加
絞り込み検索（抽出）用のリストボックスを作成したい場合等、重複データを削除したいケースがあります。重複データを削除するのにDictionaryオブジェクトを利用します。
Dictionaryオブジェクトは連想配列とも呼ばれるそうですが、配列はインデックス番号と要素がセットで格納されるのに対し、連想配列はキーとデータがセットで格納されます。この「キー」が重複を許さない仕様を利用して重複データを削除（＝重複したデータは追加しない）します。
Dictionaryオブジェクトについてはこのサイトを参考にしました。
挿入→ユーザーフォームでフォームを作成し、表示→ツールボックスでリストボックスを選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。
テーブルA列に重複削除したいフィールドがあるテーブルを用意して下さい。スクショの場合、内野手,外野手,投手,捕手,がリストボックスに追加されます。
Private Sub UserForm_Initialize()
Dim dicオブジェクト As Object
Dim r As Variant, s As Variant
Dim バッファ As String
With Worksheets("Sheet1")
Set dicオブジェクト = CreateObject("Scripting.Dictionary") '※1
If .Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Exit Sub
For Each r In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
バッファ = r.Value
If Not dicオブジェクト.Exists(バッファ) Then '※2
dicオブジェクト.Add バッファ, ""
End If
Next r
End With
For Each s In dicオブジェクト.Keys '※3
ListBox1.AddItem s
Next s
Set dicオブジェクト = Nothing
ListBox1.ListIndex = 0
End Sub- 7行目…Dictionaryオブジェクトは外部ライブラリ（Microsoft Scripting Runtime）のクラスです。参照設定しているのであれば、2行目はDim dicオブジェクト As New DictionaryでOKです（可能ならこちらを推奨）。
- 12行目…キーに変数バッファ、データには“”を代入しています。今回は「キーが重複を許さない仕様」のみを利用しているので、データは何でも構いません。必要なのは「キー」だけです。
- 18~20行目…キーdicオブジェクト.KeysをAddItemメソッドでリストボックスに追加します。
参照設定についてはこの記事で紹介しています。
番外編：CreateObjectが原因でエラー
前項コードの18~20行目、Keysメソッドでキーを取り出そうとした場合、CreateObject関数を使用しているとエラーになります。
Private Sub UserForm_Initialize()
Dim dicオブジェクト As Object
Dim r As Variant
Dim i As Long
Dim バッファ As String
With Worksheets("Sheet1")
Set dicオブジェクト = CreateObject("Scripting.Dictionary")
If .Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Exit Sub
For Each r In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
バッファ = r.Value
If Not dicオブジェクト.Exists(バッファ) Then
dicオブジェクト.Add バッファ, ""
End If
Next r
End With
'ループカウンタでKeysを1件ずつ取り出そうとした場合
For i = 0 To dicオブジェクト.Count - 1
ListBox1.AddItem dicオブジェクト.Keys(i) '※1 ここでエラーが出る
Next i
Set dicオブジェクト = Nothing
ListBox1.ListIndex = 0
End Sub- 19行目実行時に「Property Let プロシージャが定義されておらず、Property Get プロシージャからオブジェクトが返されませんでした」とエラーを吐かれます(エラー 451)。
色々調べて、回避する方法を3つ見つけました。
dicオブジェクト.Keysを変数に入れます。VBAリファレンスにはこの方法が載っているので、これが正式な記述方法です。
Dim vKey as Variant
vKey = dicオブジェクト.Keys
For i = 0 To dicオブジェクト.Count - 1
ListBox1.AddItem vKey(i)
Next idicオブジェクト.Keysの直後に()を追加します。理屈はよくわかりません。
For i = 0 To dicオブジェクト.Count - 1
ListBox1.AddItem dicオブジェクト.Keys()(i)
Next i参照設定を使用するコードだと、そもそものエラーは出ません。何がなんだかわからない…
Private Sub UserForm_Initialize()
Dim r As Variant
Dim i As Long
Dim バッファ As String
Dim dicオブジェクト As New Dictionary
With Worksheets("Sheet1")
If .Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Exit Sub
For Each r In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
バッファ = r.Value
If Not dicオブジェクト.Exists(バッファ) Then
dicオブジェクト.Add バッファ, ""
End If
Next r
End With
For i = 0 To dicオブジェクト.Count - 1
ListBox1.AddItem dicオブジェクト.Keys(i) 'エラーが出ない
Next i
Set dicオブジェクト = Nothing
ListBox1.ListIndex = 0
End Sub理解はできていませんが、エラーの回避方法でした。
リストボックスへデータを追加する方法を4パターン紹介しました。AddItemメソッドを使用する方法、Listプロパティを使用する方法、RowSourceプロパティを使用する方法、それぞれにいいところがあります。
ここでは触れませんでしたが2列以上のデータを扱う場合はAddItemメソッドでは対応できませんし、AddItem、Clear、RemoveItemを使用しないのであれば、RowSourceプロパティは大変お手軽で便利です。
一つの方法に固執するのではなく、状況に応じて使い分ける事をおすすめします。