【エクセルVBA】AddItemメソッドでリストボックスに値を追加してみよう！

皆様こんにちは、ノグチです。

ユーザーフォームを作って使ってみるまでを目標に、連載記事でコントロールのメソッドやプロパティ、イベントをご紹介しています。

前回記事では、リストボックスのRowSourceプロパティにワークシートの範囲を指定することで、リストボックスにワークシートの範囲にある値を表示する方法をご紹介しました。

【エクセルVBA】リストボックスのRowSourceプロパティの値を変動させてみよう！

エクセルのユーザーフォームを作って使うことを目標に、連載記事でコントロールのメソッドやプロパティ、イベントをご紹介しています。今回は、リストボックスのRowSourceプロパティをVBAで指定することで、他のコントロールの入力値によってリストボックスに表示する値を変動させる方法をご紹介しています。

しかし前回記事では、「結局、リストボックスにはワークシート上の範囲にある値しか表示できないの？」というモヤモヤが残ってしまいました。

今回はそのモヤモヤを解消すべく、リストボックスのAddItemメソッドを使って、ワークシートの範囲ではない任意の値をリストボックスの値としてセットする方法をご紹介します。

前回までのおさらい
AddItemメソッドの記述方法
AddItemメソッドでリストボックスに値をセットする
1. AddItemメソッドで検索結果をリストボックスに追加するコード
2. 検索にヒットした値をAddItemメソッドでリストボックスに追加する
RowSourceプロパティとの併用はできない
最後に
連載目次：【エクセルVBA】ユーザーフォームを使ってみよう！

前回までのおさらい

毎度おなじみ、前回までに作成したユーザーフォームがこちら。

前回は、「品名」のテキストボックスに入力した値からこちらのワークシートを検索して…

検索にヒットした値をワークシート上に書き出し、書き出した値を「生産地」リストボックスのRowSourceプロパティにセットして、リストボックスに検索結果を表示させたのでした。

そして、前回作成した検索用のコードがこちら。

Private Sub TextItem_AfterUpdate()
With ActiveSheet
    .Range("E:E").Clear
    Dim myRange As Range
    Dim rngSearch As Range
    Dim strAddress As String

    Set myRange = .Range("A2:A13")
    Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart)
    Dim i As Long
    i = 1

    If Not rngSearch Is Nothing Then
        .Cells(i, 5).Value = .Cells(rngSearch.Row, rngSearch.Column + 1).Value
        strAddress = rngSearch.Address
        Do
            Set rngSearch = myRange.FindNext(rngSearch)
            If rngSearch Is Nothing Then
                Exit Do
            Else
                If strAddress <> rngSearch.Address Then
                   i = i + 1
                   .Cells(i, 5).Value = .Cells(rngSearch.Row, rngSearch.Column + 1).Value
                End If
             End If
        Loop While rngSearch.Address <> strAddress
    Else
        .Cells(i, 5).Value = "該当なし"
    End If
    'リストボックスに検索結果の範囲をセット
    ListRegion.RowSource = .Range(.Cells(1, 5), .Cells(i, 5)).Address
    End With
End Sub

今回は、こちらのコードを、リストボックスのAddItemメソッドを使った処理に置き換えていきましょう。

AddItemメソッドの記述方法

まずはAddItemメソッドの記述方法から。

リストボックスにRowSourceプロパティを使わないで値を追加する場合、AddItemメソッドを使って下記のように記述します。

ListBoxオブジェクト.AddItem 追加したい値(文字列型), 追加したい順序(数値型)

追加したい順序は、AddItemメソッドで追加しようとしている値をリストの何番目に入れたいのか、を指定するパラメータです。

省略可能で、指定しなければリストの一番下に入れてくれます。

指定したい場合は数値を指定すればよいのですが、先頭が1ではなく0ですので、注意が必要です。

3行以上あるリストの3番目に追加したい場合は、0から3つ数えて「2」を指定することになりますね。

AddItemメソッドでリストボックスに値をセットする

AddItemメソッドで検索結果をリストボックスに追加するコード

では早速、AddItemメソッドを使ってリストボックスに値を追加してみましょう。

コードはこちら。

Private Sub TextItem_AfterUpdate()
With ActiveSheet

   Dim myRange As Range
   Dim rngSearch As Range

   Set myRange = .Range("A2:A13")
   Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart)

   If Not rngSearch Is Nothing Then
        ListRegion.AddItem .Cells(rngSearch.Row, rngSearch.Column + 1).Value
        strAddress = rngSearch.Address
        Do
            Set rngSearch = myRange.FindNext(rngSearch)
            If rngSearch Is Nothing Then
                Exit Do
            Else
                If strAddress <> rngSearch.Address Then
                    ListRegion.AddItem .Cells(rngSearch.Row, rngSearch.Column + 1).Value
                End If
            End If
        Loop While rngSearch.Address <> strAddress
    End If

End With
End Sub

前回のRowSouceプロパティを使う方法の、検索にヒットした値の処理部分を、AddItemメソッドでリストボックスに追加するように変更しています。