コンボボックスで複数列のリスト設定と値の取得方法

ユーザーフォーム講座 動画編 10回
コンボボックスで複数列のリスト設定と値の取得方法

動画でExcel コンボボックスで複数列のリスト設定と値の取得方法

【VBAユーザーフォーム】コンボボックスで複数列4列表示する使いかた、選択アイテムを取得する、リスト項目を追加する方法。基礎編第10回

動画版「ユーザーフォーム講座」です。
ユーザーフォーム基礎編の10回となります。 前回は「コンボボックスに複数列を表示する」という内容でお送りしました。
複数列をリストに取り込む方法を中心にお話ししたんですが、 複数列を取り込む方法といっても何通りかあって、 そのうちの自分の使い方に一番合った方法を、取り入れて使えばいいんという内容でした。

コンボボックスで複数列4列表示する動画 ユーザーフォーム

https://youtu.be/Ni17Dm5ux80

4列とか5列の複数列

今回は複数列とは言っても、もっと多い場合です。
4列とか5列を取り込む場合にはどうしたらいいのか、 という内容です。
複数列の範囲をどうやってリストに取り込んだらいいのか、
その列が固定したものじゃなくて変わってしまう可能性もあるとしたら、どのように取り込んだらいいのかというような内容です。

NAMAE1

また取り込んだ列を取得する方法、 選択した値をどうやって取得するのか?
バリューとテキストは取得できるということが前回でもわかっていますが、 それ以外の列はどのように取り込んだらいいのか、 というようなことがわからないと困ってしまいます。

NAMAE2

4列などの複数列をコンボボックスリストに表示させるには、行く通りも方法があります。

NAMAE3

直接プロパティウィンドウのRowSourceに書く

下図のサンプルでは、プロパティウィンドウのローソースプロパティに直に書いてしまうという方法です。
シート名から指定しています。"Sheet1!A2:D21"

NAMAE4

範囲は指定できましたが、4列取得したければそれだけではダメです。
さらに、カラムカウントというプロパティで列数を指定します。 4列ですよと教えてやらないと4列取りません。
そして真上にあるのは、バウンドカラムです。これはバリューをどこから取ってくるかというそのカラムを指定するプロパティです。
ここでは2としています。 2列目をバリューとするということです。
もう一つの設定で下の方にあるTextColumnというプロパティには1を入れてください。

NAMAE5

コンボボックスのリストに見出しをつける

リストに見出しをつけるならば、ColumnHeads true としてください。
またきれいに整って表示させるためには、列幅の設定も必要です。これはColumnWidthsにポイントで指定します。

NAMAE6

起動させてみましょう。どうも2列目の列幅が狭いようです。その都度確認して調整しましょう。

NAMAE7

データを取得しました。ただしデータはTextColumnとBoundColumnしか設定していないので、2つしか取得できませんね。

NAMAE8

コードを見てみましょう。フォームを閉じるボタンのコードと、データを取得ボタンのコードです。

NAMAE9
Option Explicit
Dim ws As Worksheet, DRng As Range
Private Sub btn_write_Click()
     If ComboBox1.ListIndex >= -1 Then
     lbl_print.Caption = vbLf & ComboBox1.Text & _
          " " & ComboBox1.Value
     End If
End Sub

UserForm_Initializeで配列で読み込む場合

前回も解説したように、リストの指定は、シートから配列でリストを読み込むのが簡単です。 その場合のコードです。
”.List = ws.Range("A2:D21").Value”
配列で入れる場合は、このように1行で簡潔にコードを書くことができます。
配列を使うために、わざわざ変数を宣言する必要はありません。Listはもともと配列だからです。

NAMAE10
Private Sub UserForm_Initialize()
    Set ws = Worksheets("Sheet1")
    With Me.ComboBox1
        .ColumnCount = 4
        .List = ws.Range("A2:D21").Value
    End With
End Sub

配列でリストを設定した場合は、ColumnHeadsが入りませんから、ColumnHeads をFalse にしてください。treuのままだと、空の見出しが入ってしまいます。

NAMAE11

フォームを起動しました。ColumnHeadsをFallsにしたので、見出しの行が消えています。
見出しが必要であれば、ラベルを利用して疑似見出しを作成しましょう。

NAMAE13

ComboBox1のListプロパティで可変の配列を入れたい

ただしリストのサイズが変更される場合には、その都度書き換えてる必要があります。
マクロを使うからには自動でサイズを取得して手間なしにしたいものです。
その場合にはこのようなコードを書くと良いでしょう。

NAMAE12
     Option Explicit
     Dim ws As Worksheet, DRng As Range
     Private Sub UserForm_Initialize()
          Set ws = Worksheets("Sheet1")
          With ws
              Set DRng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp).Offset(, 3))
          End With
          With ComboBox1
              .ColumnCount = 4
              .List = DRng.Value
          End With
      End Sub
     

ComboBox1の複数列Listからデータを取得する

ではデータの取得と書き出しはどうするかです。
書き出しについて今まではデータをテキストとバリューだけ取得していました。
プロパティウィンドではその2つしか設定できませんが、取得の方法があります。
Listは配列です。配列にはIndexがふってあります。
ですから必ずIndexがあるので、それを指定することで値を取得できます。

NAMAE14

コードの解説です。始めに変数SelectRowを宣言します。
”SelectRow = ComboBox1.ListIndex”
SelectRowは、ComboBox1.ListIndexの事です。
ユーザーが何行目かを選んだら、そのIndexがSelectRowということになります。

もしそのselect rowが-1より大きかったら、-1じゃなかったら、
”If SelectRow > -1 Then”
それより大きかったらということは、何か選択されているということです。
-1の時は選択されてないので、 何も選択されてないなら
MsgBox "何も選択されていません。"
と表示してプロシージャを抜けちゃうのです。

NAMAE16

選択されていたら、配列は0始まりで、0は氏名の欄なので、他を取得することにします。読み仮名と性別、年齢を取得するには、 ComboBox1.List(SelectRow, 1) で読み仮名
ComboBox1.List(SelectRow, 2) で性別
ComboBox1.List(SelectRow, 3) で年齢を取得できます。

それで同じ内容をラベルにも表示しましょうということで、
lbl_print.Caption = の後ろに上記のコードを続けて記述すればいいわけです。

NAMAE18
 lbl_print.Caption = vbLf & ComboBox1.Text & "の読み仮名は: " _
& ComboBox1.List(SelectRow, 1) & vbLf & _
"性別は: " & ComboBox1.List(SelectRow, 2) & vbLf & _
"年齢は: " & ComboBox1.List(SelectRow, 3) & "です。"

このような形で自由にデータを取れますし、 ラベルに表示するのではなくて、 ワークシートの値としてセルに転記するということも簡単にできます。