[Excel]VBA ユーザーフォームにリストボックスを追加する方法をパターン別に紹介

エクセルファイルの操作を間違えないよう誘導するのに、ユーザーフォームは非常に便利です。ユーザーフォームの中でも使用頻度の高いリストボックスへデータを追加する方法を紹介します。

コードのみで完結するリストボックス

準備

[挿入]→[ユーザーフォーム]でフォームを作成し、[表示]→[ツールボックス]で「リストボックス」を選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。

追加するデータを全てコードの中で指定する方法です。ファイルを操作する人にデータを触られたくないケースや、一度作ってしまえばほぼデータの更新がないケースはこの方法がおすすめです。

フォームモジュールでUserForm_Initializeイベントを使用して、リストボックスにデータを追加します。

UserForm1(フォームモジュール)

Private Sub UserForm_Initialize()
    Dim arr() As Variant
    Dim r As Variant

    arr = Array("内野手", "外野手", "投手", "捕手")
    
    For Each r In arr
        ListBox1.AddItem r
    Next r
    
    ListBox1.ListIndex = 0
End Sub
  • 5行目…Array関数を使って、配列の要素としてデータを指定しています。
  • 8行目…AddItemメソッドでデータを追加しています。
  • 11行目…デフォルトのカーソル位置を指定しています。「-1」で非選択状態になります。

ワークシートのデータを利用するリストボックス

シートのデータを参照するのに「名前の定義」を使用します。OFFSET関数で参照範囲を可変させているので、データを更新(追加・削除)してもコードの変更は不要です。ただし、後述しますがデータの更新をリストボックスに反映するにはひと手間必要です。

準備1

[挿入]→[ユーザーフォーム]でフォームを作成し、[表示]→[ツールボックス]で「リストボックス」を選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。

準備2

リストボックスに追加するデータを定義します。A1行目がフィールド名、以降をフィールドとして、参照範囲を「 =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1) 」とします。ただし、データが空だとフィールド名を参照してしまいます。

Listプロパティでデータを追加

ListBoxのデータは2次元配列です。前項のAddItemメソッドは空の配列に1件ずつデータを追加しますが、ワークシートのデータを利用すれば、Listプロパティでまとめて追加できます。

UserForm1(フォームモジュール)

Private Sub UserForm_Initialize()
   Dim r As Variant
    
    With ListBox1
        .List = Range("リスト").Value
        .ListIndex = 0
    End With
End Sub
  • 4行目…Range(“名前”).Valueで、定義した名前を配列として扱えます(Value省略不可)。
データが更新(追加/削除)されても即座にはリストボックスに反映されません。再イニシャライズするか、一度データをクリア(Clearメソッド)して再登録する必要があります。

RowSourceプロパティでデータを追加

このパターンが一番おすすめです。このプロパティを使用すると、リストボックスのメソッドであるAddItemClearRemoveItemが使用できなくなります(=コード上でデータの更新ができない)。

が、 RowSourceプロパティ と「名前の定義」を組み合わせる事で、シート上のデータ更新を即時リストボックスへ反映させる事ができます。

UserForm1(フォームモジュール)

Private Sub UserForm_Initialize()
    Dim r As Variant
    
    With ListBox1
        .RowSource = "リスト"
        .ListIndex = 0
    End With
End Sub
  • 5行目…RowSourceプロパティは文字列で指定する必要があります。Rangeオブジェクトを使用するならRowSource = Range(“リスト”).Addressとします。

重複データを削除したリストボックス

準備1

[挿入]→[ユーザーフォーム]でフォームを作成し、[表示]→[ツールボックス]で「リストボックス」を選択、フォーム内に適当なサイズで作成します。フォントはプロパティウィンドウで調整して下さい。

準備2

テーブルA列に重複削除したいフィールドがあるテーブルを用意して下さい。スクショの場合、「内野手」「外野手」「投手」「捕手」がリストボックスに追加されます。

絞り込み検索(抽出)用のリストボックスを作成したい場合等、重複データを削除したいケースがあります。重複データを削除するのにDictionaryオブジェクトを利用します。

Dictionaryオブジェクト は連想配列とも呼ばれるそうですが、配列はインデックス番号と要素がセットで格納されるのに対し、連想配列はキーとデータがセットで格納されます。この「キー」が重複を許さない仕様を利用して重複データを削除(=重複したデータは追加しない)します。

Dictionaryオブジェクト についてはこのサイトを参考にしました。

エクセルの神髄

「Dictionaryオブジェクトについて簡単な使用例を上げて解説して欲しいです。」との要望をいただいたので、Dicti…

UserForm1(フォームモジュール)

Private Sub UserForm_Initialize()
    Dim mydic As Object
    Dim r As Variant, vKey As Variant
    Dim buf As String

    With Worksheets("Sheet1")
        Set mydic = 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))
            buf = r.Value
            If Not mydic.Exists(buf) Then
                mydic.Add buf, ""
            End If
        Next r
    End With

    For Each vKey In mydic.Keys
        ListBox1.AddItem vKey
    Next vKey

    Set mydic = Nothing
    ListBox1.ListIndex = 0
End Sub

  • 6行目…Dictionaryオブジェクトは外部ライブラリ(Microsoft Scripting Runtime)のクラスです。参照設定しているのであれば、Dim myDic As New DictionaryOKです(可能ならこちらを推奨)。
  • 12行目…キーに変数buf、データには “” を代入しています。今回は「キーが重複を許さない仕様」のみを利用しているので、データは何でも構いません。必要なのは「キー」だけです。
  • 18~20行目…キー(mydic.Keys)をAddItemメソッドでリストボックスに追加します。

参照設定についてはこの記事で紹介しています。

参照設定とCreateObject

エクセルVBAでは、外部ライブラリ(エクセル外のオブジェクト)を参照する事によりエクセルには無い機能を利用する事ができます。方法は2つあって「参照設定」と「CreateObject関数」です。 [adcode] 参照設定 […]

CreateObjectが原因でエラー

前項コードの18~20行目、 Keysメソッドでキーを取り出そうとした場合、CreateObject関数を使用しているとエラーになります。

エラーサンプル

Private Sub UserForm_Initialize()
    Dim mydic As Object
    Dim r As Variant
    Dim i As Long
    Dim buf As String

    With Worksheets("Sheet1")
        Set mydic = 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))
            buf = r.Value
            If Not mydic.Exists(buf) Then
                mydic.Add buf, ""
            End If
        Next r
    End With
'ループカウンタでKeysを1件ずつ取り出そうとした場合
    For i = 0 To mydic.Count - 1
        ListBox1.AddItem mydic.Keys(i) 'ここでエラーが出る
    Next i

    Set mydic = Nothing
    ListBox1.ListIndex = 0
End Sub
  • 19行目実行時に「Property Let プロシージャが定義されておらず、Property Get プロシージャからオブジェクトが返されませんでした 」とエラーを吐かれます(エラー 451)。

色々調べて、回避する方法を3つ見つけました。

パターン1:()追加

パターン1

    For i = 0 To mydic.Count - 1
        ListBox1.AddItem mydic.Keys()(i)
    Next i

mydic.Keysの直後に()を追加します。 理屈はよくわかりません。

パターン2:変数追加

パターン2

    Dim vKey as Variant
    vKey = mydic.Keys

    For i = 0 To mydic.Count - 1
        ListBox1.AddItem vKey(i)
    Next i

mydic.Keysを変数に入れます。これでエラーが回避できる理由がさっぱりわからない…

パターン3: Microsoft Scripting Runtimeを参照設定する

パターン3

Private Sub UserForm_Initialize()
    Dim r As Variant
    Dim i As Long
    Dim buf As String
    Dim mydic 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))
            buf = r.Value
            If Not mydic.Exists(buf) Then
                mydic.Add buf, ""
            End If
        Next r
    End With

    For i = 0 To mydic.Count - 1
        ListBox1.AddItem mydic.Keys(i) 'エラーが出ない
    Next i

    Set mydic = Nothing
    ListBox1.ListIndex = 0
End Sub

極めつけはコレ。参照設定を使用するコードだと、そもそものエラーは出ません。何がなんだかわからない…

Dictionaryオブジェクト本来の使い方ではないからエラーになる、と私は今のところ理解をあきらめています。エラーの回避方法ではなく、理由を知りたい方は…頑張ってググって下さい。

リストボックスへデータを追加する方法を4パターン紹介しました。AddItemメソッドを使用する方法、Listプロパティを使用する方法、RowSourceプロパティを使用する方法、それぞれにいいところがあります。

ここでは触れませんでしたが2列以上のデータを扱う場合、Listプロパティは必須ですし、AddItemClearRemoveItemを使用しないのであれば、RowSourceプロパティ は大変お手軽で便利です。

一つの方法に固執するのではなく、状況に応じて使い分ける事をおすすめします。

では、最後までご高覧いただきありがとうございました。