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

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

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

前回の記事では、ユーザーフォームにリストボックスを追加する方法と、リストボックスのプロパティをいくつかご紹介しました。

エクセルのユーザーフォームでフォームを作って、使ってみることを目標に、連載記事でユーザーフォームのイベントやプロパティ、メソッドをご紹介しています。今回は、入力者に特定の値から選択させたいようなときに使える、リストボックスをフォームに追加する方法と、代表的なプロパティをご紹介しています。

今回は、前回ご紹介したリストボックスのRowSourceプロパティを、VBAで変更する方法をご紹介します！

前回までのおさらい
プロパティはVBAでも設定できる
RowSorceプロパティをVBAで設定する記述方法
ユーザーフォームの入力値によってリストボックスに表示する値を変える
1. 検索結果をRowSourceプロパティにセットするコード
2. テキストボックスの入力値でリストボックスに表示する値を変える
注意点:RowSourceプロパティは文字型で指定する
ワークシート範囲以外をリストボックスに指定したい！
最後に
連載目次：【エクセルVBA】ユーザーフォームを使ってみよう！

前回までのおさらい

これまでの記事で作成したユーザーフォームがこちら。

前回記事では、画面下部の「生産地」というラベルの下に配置してあるリストボックスを追加して、「LitRegion」というオブジェクト名をつけたのでした。

今回は、こちらのリストボックスに表示する値を、プロパティシートを使わずに、VBAで変えてみようというわけです。

プロパティはVBAでも設定できる

これまでユーザーフォームのプロパティをいくつかご紹介してきました。

記事の中では、プロパティはプロパティシートで値を設定していましたが、実はVBAでもプロパティの値を設定することができるんです。

ということで、前回記事の中でご紹介した、リストボックスに表示する値を設定する、RowSourceプロパティをVBAで設定してみましょう！

RowSorceプロパティをVBAで設定する記述方法

まず、VBAでRowSourceプロパティを設定するには、以下のように記述します。

ListBoxオブジェクト.RowSourceプロパティ = ワークシートの範囲(文字列型)

例えば、ListRegionというオブジェクト名のリストボックスのRowSourceプロパティに、ワークシートのA1セルからA5セルの範囲を指定したい場合は、このように記述することになります。

ListRegion.RowSource = "$A1:$A5"

ユーザーフォームの入力値によってリストボックスに表示する値を変える

検索結果をRowSourceプロパティにセットするコード

RowSourceプロパティをVBAで設定できることをご紹介したところで、他のコントロールの値によってリストボックスに表示する値を変化させてみましょう。

こちらの表のA列からユーザーフォームのテキストボックスに入力された値を検索して、検索にヒットしたすべてのレコードのB列「生産地」の値をリストボックスに表示していきたいと思います。

コードはこちら。

Private Sub TextItem_AfterUpdate()
With ActiveSheet

    .Range("E:E").Clear

    Dim myRange As Range
    Dim rngSearch As Range

    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
        Do
            Set rngSearch = myRange.FindNext(rngSearch)
            If rngSearch Is Nothing Then
                Exit Do
            Else
                i = i + 1
                .Cells(i, 5).Value = .Cells(rngSearch.Row, rngSearch.Column + 1).Value
            End If
        Loop Until rngSearch.Row = myRange.Row + myRange.Rows.Count - 1
    Else
        .Cells(i, 5).Value = "該当なし"
    End If

    'リストボックスに検索結果の範囲をセット
    ListRegion.RowSource = .Range(.Cells(1, 5), .Cells(i, 5)).Address

    End With
End Sub

テキストボックスの値によってリストボックスの値を変動させたいので、テキストボックスのAfterUpdateイベントを使っています。

このコードの中でやっていることは、

テキストボックスに入力された値をワークシートのA列から検索
検索にヒットしたレコードのB列の値を、ワークシートのE列に書き出し
検索が終わったらリストボックスのRowSourceプロパティに、E列に書き出した検索結果の範囲をセット

の3ステップです。

テキストボックスの入力値でリストボックスに表示する値を変える

では、上のコードを実際に動かしてみましょう。

まず、ユーザーフォームのテキストボックスに「りんご」と入力してみます。

ワークシートのB列に「りんご」は3レコード存在していますので、各レコードのB列にある、「青森」、「長野」、「岩手」の3つがリストボックスに表示されるはずです。

実際にテキストボックスに「りんご」と入力してみると…

この通り、「青森」、「長野」、「岩手」3つの値がリストボックスに表示されました！

注意点:RowSourceプロパティは文字型で指定する

注意が必要なのは、上のコードの31行目にある、RowSourceプロパティにワークシートの範囲をセットしているところです。

RangeオブジェクトのAddressプロパティを指定していますよね。

「範囲」なんだからRangeオブジェクトを指定しておけばいいんじゃないの？と、こんな風に記述してしまうかもしれません。

ListRegion.RowSource = .Range(.Cells(1, 5), .Cells(i, 5))

日本語で「範囲」と表現するので忘れてしまいがちですが、RowSourceプロパティには、「範囲を文字列で」指定します。

Rangeオブジェクトそのまま指定してしまうと、Range型をプロパティに指定してしまうことになります。

これではRowSourceプロパティに指定できる型とそもそも異なりますので、こんなエラーになってしまいます。

Rangeオブジェクトの範囲を指定したい場合は、Addressプロパティで文字列型の範囲を取得して、RowSourceプロパティに設定するようにしましょう。

ワークシート範囲以外をリストボックスに指定したい！

ここまで、リストボックスのRouwSourceプロパティをVBAで変更する方法をご紹介してきました。

結局リストボックスの値にはワークシート上の範囲しか指定できないということなの？と思ってしまわれるかもしれませんね。

いえいえ、ワークシートの範囲を使わなくてもリストボックスの値をセットする方法があるのです。

その方法については、次回記事でご紹介していきます。

最後に

今回は、リストボックスのrowSourceプロパティをVBAで設定する方法をご紹介しました。

プロパティシートでは、各コントロールの初期値としてプロパティの設定ができますが、「入力した値によって表示する値を変化させたい！」「入力した値でコントロールの大きさや色を変えたい！」というような場合には、プロパティシートは使えません。

そこで、今回のようにコントロールのプロパティをVBAで設定するようにすれば、より柔軟にコントロールを操作することができますね。

それでは、最後までお読みいただきありがとうございました！

連載目次：【エクセルVBA】ユーザーフォームを使ってみよう！

ユーザーフォームは、ちょっとした画面なら作れてしまう、便利な機能です。

何となく敷居が高いように感じてしまいますが、順を追っていけば難しいことはありませんよ。