リストボックスRowSourceプロパティ自動更新機能VBA
ユーザーフォームのリストボックスを使う時に、表示させるリスト（項目）をセル範囲とリンク・連動させるためにプロパティのRowSourceを設定しますが、リスト（項目）追加したら当然RowSourceプロパティも修正しないといけないので面倒だと思いませんか？
そこで追加したら自動的にRowSourceプロパティを書き換える、自動更新機能を付け加えると非常に便利です。
目次
ワークシートにVBAコードを書き込む
リストボックスに追加したリストを自動的に反映させる方法として、入力済み最終行を取得してAddItem＋セル番地で解決もあります。
この方法は、RowSourceプロパティを設定せずにユーザーフォームを表示させるタイミングでVBAコードでリスト表示させるセル範囲を取得する方法です。
あわせて読みたい
セル範囲を自動取得してリストボックスの項目自動追加
リストボックスのリストの追加って自動でできないものか？ A1～A10までの範囲を超えてリストが増えたら時は、リストボックスのプロパティでRowSouceを修正したり、VBAコ...
この方法でもリストを自動更新できますが、今回はRowSourceプロパティ設定でリスト取得範囲を指定して、追加されたらRowSourceプロパティを自動的に書き換える方法をご紹介します。
Worksheet_Changeイベント準備
ワークシートにVBAコードを書き込むとは？
そう思うかもしれませんが、実はVBAコードを書き込む画面に常に表示されていますので下記画像のように、RowSourceプロパティで設定したSheet1を選択します。
すると、ワークシートに対してVBAコードが設定できる画面に切り替わっていますので、ワークシートに変更があった場合（言い換えればセルにリストが追加された時）にVBAコードで記述したプログラムが作動するようにします。
次に（General）の部分をWorksheetに変更します。
クリックすると、何やらVBAコードが記載されますが、ここでは使用しませんので気にしないでください。
次に、Worksheetの表示窓の右側にあるSelectionChangeの部分をChangeに変更します。
Changeを選択すると再びVBAコードが自動的に記載され、これで準備完了です。
ちなみに最初に記載された下記部分はここでは不要なので、消してもいいし、そのままでも構いません。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
RowSourceプロパティを自動更新
ここでは、シート1のセルA列にリスト（項目）が記載してあり、ユーザーフォーム1に配置したリストボックス1に表示させるようにVBAコードを書き込みます。
そして、「もしシート1のセルA列のセルに追加などの変更されたら、プロパティを書き換える」というようにします。
RowSourceプロパティ自動更新
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
UserForm1.ListBox1.RowSource = Target.CurrentRegion.Address
End If
End Sub
VBA解説
- シートに変更があった場合に処理をする
- もしセルA列に変更があったら
- ユーザーフォーム1のリストボックス1のリスト取得範囲は、変更されたセルを含む入力済みセル番地とする
- Ifステートメント終了
- マクロ記録終了
この方法では、空欄セルを見つけたら探すのをやめてくれるので、リストの入力済み最終セルを自動判別してくれます。
これでリスト追加するたびにRowSourceプロパティを修正する手間から解放されます。
あわせて読みたい
ユーザーフォームのリストボックスの使い方・よく使う機能12選
ユーザーフォーム使っていてちょっとめんどくさいと思った経験ありませんか？ 決まった数字や文字の入力ならば、簡単にしませんか？ リストを作成しておいて、リスト内...
リストボックス関連