大体でIT
大体でIT
2020/9/2
Excel VBAのリストボックスでリストを作成する方法と選択した値を取得する方法についてご紹介します。複数列のリスト作成と値の取得、選択した値をイベント発生で取得するVBAコードについてもご紹介します。
この記事ではリストボックスのリスト作成と選択した値の取得方法についてご紹介します。
リストボックスを使えばセルへの入力も簡単になりますし入力ミスも減らすことができるかと思います。
ワークシート上で使うActiveX コントロールについてもご紹介します。
リストボックスのリストを作成する方法と、リストボックスの値を取得する方法がわかります。
Excel VBAのユーザーフォームでリストボックスのリストを作成する方法をご紹介します。
リストボックスのリストを作成する方法で一番シンプルなのは「AddItem」を使う方法です。
このコードは「フォームの中のコード」に記載します。
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "鈴木"
.AddItem "佐藤"
.AddItem "高橋"
.AddItem "伊藤"
.AddItem "田中"
End With
End Sub
ユーザーフォームの画面を開いた際に実行する「Private Sub UserForm_Initialize()」の作成します。
このコードの中に「AddItem 〇〇」で〇〇のリストを作成しています。
リストボックスの名前は「ListBox1」なので「ListBox1.AddItem 〇〇」となります。
このVBAコードを実行するとこんなリストが作成されます。
リスト作成結果
VBAコードで記述した「鈴木~田中」までのリストが作成されています。
ワークシートからリストを作成する方法です。VBAコードは先ほどと同じように「AddItem」を使います。
Private Sub UserForm_Initialize()
With ListBox1
For i = 2 To 6
.AddItem Cells(i, 1)
Next
End With
End Sub
先ほどと違うのが作成するリストの値に「AddItem セル」というようにワークシートのセルを指定しています。
これでワークシートのセルからリストボックスにリストを作成することができます。
ワークシートからリスト作成した結果
ワークシートのセルからリストボックスのリストを作成できています。
VBAコードの中で毎回リストの値を指定していたら修正するときに大変なのでセルから値を取得すると簡単で便利です。
複数列のリストを作成したい場合は、「.ColumnCount」を使います。
複数列のリストを作成
列数を設定して、空欄のリストを追加した後に、複数リストを登録します。
Private Sub UserForm_Initialize()
With ListBox1
.ColumnCount = 3 '列数を設定
.AddItem "" '空欄のリストを追加
.List(0, 0) = "鈴木" '1列目
.List(0, 1) = 190 '2列目
.List(0, 2) = 70 '3列目
End With
End Sub
シートの値から複数列のリストを作成
シートの値から、複数行で複数列のリストを作成してみます。
リストの行数は「.ListCount」を使うと取得できて、リストは「0行目」から始まるので、「.ListCount - 1」の行に入力をするという感じです。
Private Sub UserForm_Initialize()
With ListBox1
.ColumnCount = 3 '列数を設定
For i = 2 To 6
.AddItem "" '空欄のリストを追加
.List(.ListCount - 1 , 0) = Cells(i, "A") '1列目
.List(.ListCount - 1 , 1) = Cells(i, "B") '2列目
.List(.ListCount - 1 , 2) = Cells(i, "C") '3列目
Next
End With
End Sub
ポイントは、「.ListCount」で、リストの数を取得して、最終行を取得する点になります。
上記の場合は、「5」を取得できますので、最終行は「5 - 1 = 4行目」となります。
複数列のリストをリストボックスに作成する方法を紹介します。
複数列のリストを作成するVBAコードです。こちらも「フォームの中のコード」に記載します。
Private Sub UserForm_Initialize()
Dim A
'ワークシートから値を取得
A = Range("A2:C6")
With ListBox1
.ColumnCount = 3 '3列
.List = A 'リスト作成
End With
End Sub
最初にリストにしたい複数列の値を「a = 取得したい複数列のリスト」というようにして取得します。
次にリストボックスの列数を「ColumnCount = 3」で3列と指定します。
最初に取得した複数列の値を「List = A」でリストボックスに入力します。
これで複数列のリストを、一括で作成することができます。
複数列のリスト作成結果
複数列のリストが作成されています。「ColumnCount」と「List」を使えば簡単です。
これはリストボックスの「プロパティ」を使う方法です。
リストボックスの「プロパティ」で「RowSource」の中にワークシートのセル範囲を指定します。こんな感じです。
RowSourceにセル範囲を指定
セルと連動したリスト作成結果
VBAで複雑なことはしないがとりあえずリストを作成したいといった場合に使えるかと思います。
リストボックスで選択した値を取得する方法についてご紹介します。
選択した値を取得する方法です。リストボックスのリストが1列の場合は「.Text」だけでできます。
Private Sub CommandButton1_Click()
'ボタンでセルに値を入力
Range("C2") = ListBox1.Text
End Sub
ユーザーフォームの中に新しくボタンを作成してその中に値を取得するVBAコードを記載しています。
選択した値は「ListBox1.Text」で取得できます。簡単ですね。
この場合では取得した値をセル「C2」へ入力しています。
選択した値を取得するVBAコードを実行した結果です。
選択した値を取得した結果
セル「C2」にリストボックスで選択した「伊藤」が入力されています。
複数列の場合は「ListIndex」と「List(〇, 〇)」を使って値を取得します。
VBAコードはこちらです。「フォームの中のコード」に記載します。
Private Sub CommandButton1_Click()
With ListBox1
Cells(2, "E") = .List(.ListIndex , 0) '選択行の0列目を取得
Cells(2, "F") = .List(.ListIndex , 1) '選択行の1列目を取得
Cells(2, "G") = .List(.ListIndex , 2) '選択行の2列目を取得
End With
End Sub
まずは選択したリストの行を「a = .ListIndex」で取得します。
ListIndexで選択した行数を取得
2番目の「佐藤」の行を選択しています。「ListIndex」で取得されるのは「1」になります。
次は選択した行のデータを「.List(〇, 〇)」で取得します。
「.List」で取得できるデータ配列はこんな感じです。
Listの配列構造
あとはセルから取得と似ています。CellsをListに変えるイメージです。
複数列のリストから値を取得した結果
「佐藤」の行を選択していてその列のデータが取得できています。
リストの列数は「ColumCount」で取得できますので列数が変わる場合は「ColumnCount」でデータを取得したりすると応用が利きます。
Changeイベントでリストを選択したときタイミングで値を取得する方法をご紹介します。
選択した値を毎回ボタンをクリックして値を取得するのは面倒という場合に使えます。
ChangeイベントのVBAコードです。「フォームのコード」に記載します。
Private Sub ListBox1_Change()
'選択した値を取得
Cells(2, "C") = ListBox1.Text
End Sub
選択した値が変わったときに実行するイベント「Private Sub 〇〇_Change()」を使います。
このイベントコードの中に値を取得するVBAコードを記載します。
これでリストボックスの値を選択した際にリストの値を自動で取得することができます。
選択した値を自動取得した結果
ボタンを追加しなくてもリストを選択すれば値を自動で取得することができます。
ダブルクリックのイベントでリストの値を取得する方法を説明します。
こちらはリスト内の値をダブルクリックした際にその値を取得する方法です。
こちらもボタンを追加せず値を取得できるので操作する回数を減らせます。
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'選択した値を取得
Cells(2, "C") = ListBox1.Text
End Sub
ダブルクリックで実行する「Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)」を使用しています。
このコードの中にリストの値を取得するコードを記載しています。
これでリスト内の値をダブルクリックすれば自動で値を取得することができます。
ちなみにダブルクリックのイベント「Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)」とか長いですがわざわざ手打ちで入力する必要はありません。
コードの左上の方でリストで「ListBox1」を選んで
イベントコードの設定手順①
コードの右上のリストで「DblClick」を選べば自動で出てきます。
イベントコードの設定手順②
では、ダブルクリックのイベントを使ってリストの値を取得した結果です。
ダブルクリックのイベントで値を取得
ワークシートでもリストボックスを使用することができます。
方法はフォーム コントロールとActiveX コントロールがあります。
どちらも似たようなものでActiveX コントロールの方が使い勝手がいいのでActiveX コントロールの使い方を紹介します。
ActiveX コントロールでリストボックスを使用した例です。
ActiveXのリストボックスでリスト作成と値の取得
ワークシートのセル「A2:C6」からリストを作成して、リストボックスで選択した値を取得してセルへ入力しています。
最初に「開発」タグからActiveX コントロールの「リストボックス」を選択してワークシートへリストボックスを作成します。
ActiveXのリストボックス作成
ActiveXのリストボックスを設定したいので「デザインモード」にします。
デザインモードにする
作成したActiveXのリストボックスを「右クリック」して「プロパティ」を選択します。
プロパティを選択
プロパティが表示されますのでリストと入力セルを指定します。
プロパティの設定
作成したいリストの範囲を「ListFillRange」に入力します。
リストボックスで選択した値を入力するセルは「LinkedCell」に入力します。
あとは、「Vlookup」で列の値を取得しています。
列の値を取得
これでActiveXでリストボックスのリスト作成と値の取得ができます。
この記事ではリストボックスのリスト作成と選択した値の取得方法についてご紹介しました。
リストボックスを使えばセルへの入力も簡単になりますし入力ミスも減らすことができるかと思います。
ワークシートでリストボックスを使う際はActiveX コントロールがおすすめです。
うまくリストボックスを使って入力作業を効率化しましょう。
ご参考になればと思います。最後までご覧くださいましてありがとうございました。