大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAのリストボックスでリストを作成する方法と選択した値を取得する方法についてご紹介します。複数列のリスト作成と値の取得、選択した値をイベント発生で取得するVBAコードについてもご紹介します。

はじめに

この記事ではリストボックスのリスト作成と選択した値の取得方法についてご紹介します。

リストボックスを使えばセルへの入力も簡単になりますし入力ミスも減らすことができるかと思います。

ワークシート上で使うActiveX コントロールについてもご紹介します。

この記事を読むメリット

  • リストボックスのリストを作成する方法と、リストボックスの値を取得する方法がわかります。

目次から見たい項目へ移動すると便利ですよ。

目次

Excel VBAでリストボックスのリストを作成

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コードを実行するとこんなリストが作成されます。

リスト作成結果

AddItemで作成したリスト

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コードを実行した結果がこちらです。

ワークシートからリスト作成した結果

ワークシートからリストを作成した結果

ワークシートのセルからリストボックスのリストを作成できています。

VBAコードの中で毎回リストの値を指定していたら修正するときに大変なのでセルから値を取得すると簡単で便利です。

複数列のリストを作成

複数列のリストを作成したい場合は、「.ColumnCount」を使います。

複数列のリストを作成

複数列のリストを作成するVBAコードです。

列数を設定して、空欄のリストを追加した後に、複数リストを登録します。

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」で、リストの数を取得して、最終行を取得する点になります。

.ListCountでリストの行数を取得できます

上記の場合は、「5」を取得できますので、最終行は「5 - 1 = 4行目」となります。

複数列のリストを一括で作成

複数列のリストをリストボックスに作成する方法を紹介します。

ポイントは「List」を使う点です。

複数列のリストを作成する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にセル範囲を指定

リストボックスのプロパティでRowSourceにセル範囲を指定

ここではセルの「A2:C6」を指定しています。

結果はこんな感じになります。

セルと連動したリスト作成結果

リストボックスのプロパティでRowsSourceを使ってリストを作成した結果

ワークシートからリストが作成できています。

VBAで複雑なことはしないがとりあえずリストを作成したいといった場合に使えるかと思います。

Excel VBAでリストボックスの値を取得

リストボックスで選択した値を取得する方法についてご紹介します。

選択した値を取得

選択した値を取得する方法です。リストボックスのリストが1列の場合は「.Text」だけでできます。

選択した値を取得するVBAコードです。

こちらも「フォームの中のコード」に記載します。

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で選択した行数を取得

ListIndexで選択したリストの行を取得する

2番目の「佐藤」の行を選択しています。「ListIndex」で取得されるのは「1」になります。

リストボックスでは最初の行は「0行」になります。

次は選択した行のデータを「.List(〇, 〇)」で取得します。

「.List」で取得できるデータ配列はこんな感じです。

Listの配列構造

Listで取得するデータ配列のイメージ

0行0列から始まっているのがポイントです。

あとはセルから取得と似ています。CellsをListに変えるイメージです。

複数列のリストからデータを取得した結果です。

複数列のリストから値を取得した結果

複数列のリストから値を取得した結果

「佐藤」の行を選択していてその列のデータが取得できています。

リストの列数は「ColumCount」で取得できますので列数が変わる場合は「ColumnCount」でデータを取得したりすると応用が利きます。

Changeイベントで選択した値を取得

Changeイベントでリストを選択したときタイミングで値を取得する方法をご紹介します。

選択した値を毎回ボタンをクリックして値を取得するのは面倒という場合に使えます。

ChangeイベントのVBAコードです。「フォームのコード」に記載します。

Private Sub ListBox1_Change()
    
  '選択した値を取得
  Cells(2, "C") = ListBox1.Text
    
End Sub

選択した値が変わったときに実行するイベント「Private Sub 〇〇_Change()」を使います。

このイベントコードの中に値を取得するVBAコードを記載します。

これでリストボックスの値を選択した際にリストの値を自動で取得することができます。

こんな感じです。

選択した値を自動取得した結果

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」を選んで

イベントコードの設定手順①

ListBox1を選択する

コードの右上のリストで「DblClick」を選べば自動で出てきます。

イベントコードの設定手順②

DblClickを選択する

他のコードも同じように取得できます。

ちょっと話がそれました。戻します。

では、ダブルクリックのイベントを使ってリストの値を取得した結果です。

ダブルクリックのイベントで値を取得

ダブルクリックのイベントでリストの値を取得した結果

ダブルクリックした行の値が取得できています。

ワークシートのリストボックスを使う

ワークシートでもリストボックスを使用することができます。

方法はフォーム コントロールとActiveX コントロールがあります。

どちらも似たようなものでActiveX コントロールの方が使い勝手がいいのでActiveX コントロールの使い方を紹介します。

ActiveXのリストボックス

ActiveX コントロールでリストボックスを使用した例です。

ActiveXのリストボックスでリスト作成と値の取得

ActiveX コントロールでリストボックスを使用した例

ワークシートのセル「A2:C6」からリストを作成して、リストボックスで選択した値を取得してセルへ入力しています。

セルの一部にVlookup関数を使用しています。

手順を説明します。

最初に「開発」タグからActiveX コントロールの「リストボックス」を選択してワークシートへリストボックスを作成します。

ActiveXのリストボックス作成

ActiveXのリストボックスを作成

ActiveXのリストボックスを設定したいので「デザインモード」にします。

デザインモードにする

デザインモード

作成したActiveXのリストボックスを「右クリック」して「プロパティ」を選択します。

プロパティを選択

プロパティの選択

プロパティが表示されますのでリストと入力セルを指定します。

プロパティの設定

ActiveXのリストボックスのプロパティを設定

作成したいリストの範囲を「ListFillRange」に入力します。

リストボックスで選択した値を入力するセルは「LinkedCell」に入力します。

あとは、「Vlookup」で列の値を取得しています。

列の値を取得

Vlookupで取得した値から列の値を取得

これでActiveXでリストボックスのリスト作成と値の取得ができます。

おわりに

この記事ではリストボックスのリスト作成と選択した値の取得方法についてご紹介しました。

リストボックスを使えばセルへの入力も簡単になりますし入力ミスも減らすことができるかと思います。

ワークシートでリストボックスを使う際はActiveX コントロールがおすすめです。

うまくリストボックスを使って入力作業を効率化しましょう。

ご参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す