コンボボックスで複数列のリスト設定と値の取得方法
動画でExcel コンボボックスで複数列のリスト設定と値の取得方法
【VBAユーザーフォーム】コンボボックスで複数列4列表示する使いかた、選択アイテムを取得する、リスト項目を追加する方法。基礎編第10回
動画版「ユーザーフォーム講座」です。
ユーザーフォーム基礎編の10回となります。
前回は「コンボボックスに複数列を表示する」という内容でお送りしました。
複数列をリストに取り込む方法を中心にお話ししたんですが、
複数列を取り込む方法といっても何通りかあって、
そのうちの自分の使い方に一番合った方法を、取り入れて使えばいいんという内容でした。
コンボボックスで複数列4列表示する動画 ユーザーフォーム
4列とか5列の複数列
今回は複数列とは言っても、もっと多い場合です。
4列とか5列を取り込む場合にはどうしたらいいのか、
という内容です。
複数列の範囲をどうやってリストに取り込んだらいいのか、
その列が固定したものじゃなくて変わってしまう可能性もあるとしたら、どのように取り込んだらいいのかというような内容です。
また取り込んだ列を取得する方法、
選択した値をどうやって取得するのか？
バリューとテキストは取得できるということが前回でもわかっていますが、
それ以外の列はどのように取り込んだらいいのか、
というようなことがわからないと困ってしまいます。
４列などの複数列をコンボボックスリストに表示させるには、行く通りも方法があります。
直接プロパティウィンドウのRowSourceに書く
下図のサンプルでは、プロパティウィンドウのローソースプロパティに直に書いてしまうという方法です。
シート名から指定しています。"Sheet1!A2:D21"
範囲は指定できましたが、4列取得したければそれだけではダメです。
さらに、カラムカウントというプロパティで列数を指定します。
4列ですよと教えてやらないと4列取りません。
そして真上にあるのは、バウンドカラムです。これはバリューをどこから取ってくるかというそのカラムを指定するプロパティです。
ここでは2としています。
2列目をバリューとするということです。
もう一つの設定で下の方にあるTextColumnというプロパティには1を入れてください。
コンボボックスのリストに見出しをつける
リストに見出しをつけるならば、ColumnHeads true としてください。
またきれいに整って表示させるためには、列幅の設定も必要です。これはColumnWidthsにポイントで指定します。
起動させてみましょう。どうも2列目の列幅が狭いようです。その都度確認して調整しましょう。
データを取得しました。ただしデータはTextColumnとBoundColumnしか設定していないので、２つしか取得できませんね。
コードを見てみましょう。フォームを閉じるボタンのコードと、データを取得ボタンのコードです。
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はもともと配列だからです。
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のままだと、空の見出しが入ってしまいます。
フォームを起動しました。ColumnHeadsをFallsにしたので、見出しの行が消えています。
見出しが必要であれば、ラベルを利用して疑似見出しを作成しましょう。
ComboBox1のListプロパティで可変の配列を入れたい
ただしリストのサイズが変更される場合には、その都度書き換えてる必要があります。
マクロを使うからには自動でサイズを取得して手間なしにしたいものです。
その場合にはこのようなコードを書くと良いでしょう。
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からデータを取得する
ではデータの取得と書き出しはどうするかです。
書き出しについて今まではデータをテキストとバリューだけ取得していました。
プロパティウィンドではその２つしか設定できませんが、取得の方法があります。
Listは配列です。配列にはIndexがふってあります。
ですから必ずIndexがあるので、それを指定することで値を取得できます。
コードの解説です。始めに変数SelectRowを宣言します。
”SelectRow = ComboBox1.ListIndex”
SelectRowは、ComboBox1.ListIndexの事です。
ユーザーが何行目かを選んだら、そのIndexがSelectRowということになります。
もしそのselect rowが-1より大きかったら、-1じゃなかったら、
”If SelectRow > -1 Then”
それより大きかったらということは、何か選択されているということです。
-1の時は選択されてないので、 何も選択されてないなら
MsgBox "何も選択されていません。"
と表示してプロシージャを抜けちゃうのです。
選択されていたら、配列は0始まりで、0は氏名の欄なので、他を取得することにします。読み仮名と性別、年齢を取得するには、
ComboBox1.List(SelectRow, 1) で読み仮名
ComboBox1.List(SelectRow, 2) で性別
ComboBox1.List(SelectRow, 3) で年齢を取得できます。
それで同じ内容をラベルにも表示しましょうということで、
lbl_print.Caption = の後ろに上記のコードを続けて記述すればいいわけです。
lbl_print.Caption = vbLf & ComboBox1.Text & "の読み仮名は: " _ & ComboBox1.List(SelectRow, 1) & vbLf & _ "性別は: " & ComboBox1.List(SelectRow, 2) & vbLf & _ "年齢は: " & ComboBox1.List(SelectRow, 3) & "です。"
このような形で自由にデータを取れますし、 ラベルに表示するのではなくて、 ワークシートの値としてセルに転記するということも簡単にできます。