リストボックスRowSourceプロパティ自動更新機能VBA

セルの値を自動取得してリストボックスに追加リストボックス

ユーザーフォームのリストボックスを使う時に、表示させるリスト(項目)をセル範囲とリンク・連動させるためにプロパティのRowSourceを設定しますが、リスト(項目)追加したら当然RowSourceプロパティも修正しないといけないので面倒だと思いませんか?

そこで追加したら自動的にRowSourceプロパティを書き換える、自動更新機能を付け加えると非常に便利です。

スポンサーリンク

ワークシートにVBAコードを書き込む

リストボックスに追加したリストを自動的に反映させる方法として、入力済み最終行を取得してAddItem+セル番地で解決もあります。

この方法は、RowSourceプロパティを設定せずにユーザーフォームを表示させるタイミングでVBAコードでリスト表示させるセル範囲を取得する方法です。

この方法でもリストを自動更新できますが、今回はRowSourceプロパティ設定でリスト取得範囲を指定して、追加されたらRowSourceプロパティを自動的に書き換える方法をご紹介します。

Worksheet_Changeイベント準備

ワークシートにVBAコードを書き込むとは?

そう思うかもしれませんが、実はVBAコードを書き込む画面に常に表示されていますので下記画像のように、RowSourceプロパティで設定したSheet1を選択します。

リストボックスシート選択
クリックで拡大します

すると、ワークシートに対してVBAコードが設定できる画面に切り替わっていますので、ワークシートに変更があった場合(言い換えればセルにリストが追加された時)にVBAコードで記述したプログラムが作動するようにします。

次に(General)の部分をWorksheetに変更します。

リストボックスシート選択2
クリックで拡大します

クリックすると、何やらVBAコードが記載されますが、ここでは使用しませんので気にしないでください。

リストボックスシート選択3
クリックで拡大します

次に、Worksheetの表示窓の右側にあるSelectionChangeの部分をChangeに変更します。

リストボックスシート選択4

Changeを選択すると再びVBAコードが自動的に記載され、これで準備完了です。

リストボックスシート選択5
クリックで拡大します

ちなみに最初に記載された下記部分はここでは不要なので、消してもいいし、そのままでも構いません。

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解説
  1. シートに変更があった場合に処理をする
  2. もしセルA列に変更があったら
  3. ユーザーフォーム1のリストボックス1のリスト取得範囲は、変更されたセルを含む入力済みセル番地とする
  4. Ifステートメント終了
  5. マクロ記録終了

この方法では、空欄セルを見つけたら探すのをやめてくれるので、リストの入力済み最終セルを自動判別してくれます。

これでリスト追加するたびにRowSourceプロパティを修正する手間から解放されます。

リストボックス関連

スポンサーリンク


リストボックス
スポンサーリンク
参考になりましたらシェア宜しくお願い致します

山頂テラスデッキからの三段紅葉(2019年11月)

フリーパスって助成金つぎ込んでいいの?

コロナ禍のGo To トラベル以外にも助成金だらけでうらやましいね。

安くなったフリーパスで遊びに行きたいけど、給与も減って余裕ないよね。

Excel VBA