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

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

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

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

入力済み最終行を取得してAddItem＋セル番地で解決
1. 基本の1項目（1列）だけのリストボックス
2. 2列以上の複数列をリストに自動追加

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

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

セルの値やセルの範囲をリストボックスに反映させる方法

リストボックスのリスト化をセルの範囲を反映させるためにプロパティで設定しますか？それともVBAコード1行で行いますか？

基本の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つのリストボックスに2列や複数列表示させる設定や、セルに必要な項目だけコマンドボタンで記載できるようにVBAコード書きましょう。

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

列数・列幅・リストを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解説