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

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

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

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

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

スポンサーリンク

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

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

基本の1項目(1列)だけのリストボックス

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

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

  1. 繰り返し処理のFor~Next
  2. 入力済み最終行を取得する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解説
  1. ユーザーフォームイニシャライズイベントを実行する
  2. 繰り返し処理の変数「i」は1~入力済み最終行
  3. リストボックス1のアイテムはセルi行目の1列目(セルA1)
  4. 繰り返す
  5. マクロ記録終了

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

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

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

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

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

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

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

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

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

列数・列幅・リストを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~NextCells(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解説
  1. ユーザーフォームイニシャライズイベントを実行する
  2. ユーザーフォーム1のリストボックス1を扱う
  3. 列の数は2列とする
  4. 列の幅は1列目150、2列目30とする
  5. 繰り返し処理の変数「i」は1~入力済み最終行
  6. リスト項目の
  7. 最初の行の1列目はセルi行目の1列目(A列)
  8. 最初の行の2列目はセルi行目の2列目(B列)
  9. 繰り返す
  10. マクロ記録終了

ここでListCountは行数なので、-1を付け加えています。

理由はリストボックスの1行目は「1」ではなく「0」なので-1をすることで0を表すことができるのです。

列数も同じく「0」が1列目、「1」が2列目なので、.List(ListCount – 1,0)」で最初の行の最初の列でもあるA1セルが指し示しています。

ここで使用したUserForm_Initializeはとても便利なので高頻度で使用していますので、ぜひマスターしてくださいね。

リストボックス関連

スポンサーリンク


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

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

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

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

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

Excel VBA