セル範囲を自動取得してリストボックスの項目自動追加

2021年5月22日2024年9月10日

リストボックスのリストの追加って自動でできないものか？

A1～A10までの範囲を超えてリストが増えたら時は、リストボックスのプロパティでRowSouceを修正したり、VBAコードでAddItemを追加記載すればできますが、リストを増やす時にわざわざVBAコード書き足すの面倒と思ったことありませんか？

セルの範囲を事前にRowSourceで決めないで、ユーザーフォームが表示されるたびに入力済み最終行を取得すれば解決です。

入力済み最終行を取得してAddItem＋セル番地で解決

リストボックスに表示させる値を取得・反映させる方法はいくつかありますが、ここでは省略しますので詳しくは下記をご覧くださいませ。

基本の1項目（1列）だけのリストボックス

ここで使用するリストボックスに表示させる方法は、ListBox1.AddItem セル番地です。

ここに下記2つを組み合わせて簡単なVBAコードを書いていきます。

繰り返し処理のFor～Next
入力済み最終行を取得するCells(Rows.Count, 1).End(xlUp).Row

リストボックスリスト自動追加

Private Sub UserForm_Initialize()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.AddItem Cells(i, 1)
Next i
End Sub

VBA解説

ユーザーフォームイニシャライズイベントを実行する
繰り返し処理の変数「i」は1～入力済み最終行
リストボックス1のアイテムはセルi行目の1列目（セルA1）
繰り返す
マクロ記録終了

これで入力済み最終行が10行目ならば、セル（10,1）となりA10になります。

そして繰り返し処理が1行目から10行目まで順番にAddItem登録を繰り返すので、全部で10行分が登録されます。

さらにユーザーフォームが表示される前に、これらの作業を行ったうえで表示するUserForm_InitializeにVBAコードを記載して完成です。

もし、セルA11に新たに値が入力されると最終行が11行目になったと判断して11行分登録されますから、ある意味自動的にリスト追加ができるようになりました。

これで基本の1列だけの時はマスターできましたので、次に応用編のリストが2列以上ある複数列を自動追加できるようにしましょう。

2列以上の複数列をリストに自動追加

ユーザーフォームのリストボックスは1列だけではなく2列以上の複数列をリスト化することができます。

リストボックスの2列以上の複数列は初期設定では対応していませんが、簡単に対応可能になりますので基本的な設定方法は下記をご覧くださいませ。

あわせて読みたい

リストボックスを2列・複数列表示やセルに記載する方法あらかじめ指定した複数の値の中から選択することができるリストボックス。初期設定（デフォルト）では1列（1項目）だけの表示・選択なのですが、実は2列・3列と複数列...

ここでは上記基本複数列対応の応用編で、自動的に複数列項目をリストボックスに追加できるようにします。

列数・列幅・リストをVBAで設定

Private Sub UserForm_Initialize()
With UserForm1.Controls(“ListBox1”)
.ColumnCount = 2
.ColumnWidths = “30;70”
.AddItem
.List(0, 0) = 1
.List(0, 1) = “エクセル”
.AddItem
.List(1, 0) = 2
.List(1, 1) = “Excel”
End With
End Sub

上記VBAコードは「リストボックスを2列・複数列表示やセルに記載する方法」でもご紹介している、リストが増えた場合は手動で増やすことができる基本的なVBAコードです。

これを改良して2列自動追加対応にしますので、1列の時と同じようにFor～Next、Cells(Rows.Count, 1).End(xlUp).Row、を付け加えて、青色マーカーの部分にリストを数えるListCountプロパティを使えば完成です。

2列リストボックスリスト自動追加

Private Sub UserForm_Initialize()
With UserForm1.Controls(“ListBox1”)
.ColumnCount = 2
.ColumnWidths = “150;30”
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
.AddItem
.List(.ListCount – 1, 0) = Cells(i, 1)
.List(.ListCount – 1, 1) = Cells(i, 2)
Next i
End With
End Sub

VBA解説