Excel VBA リストボックスの複数列表示の方法について

質問

すいません、エクセルVBAのユーザーフォームのリストボックスの表示方法について質問があります。
シートのセルに
　　 A列 　 D列　　　G列
1行 りんご　　赤　　　120円
2行　みかん　　黄　　　130円
3行　すいか　　緑 110円
4行　りんご　　赤 160円
. ・・・　　・　　　・・・
.

と、50行まで値を入れます。
VBAでユーザーフォームを挿入し、

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "りんご"
.AddItem "みかん"
.AddItem "すいか"
End With
End Sub
でコンボボックスの値を設定し、次に
Private Sub ComboBox1_Change()
Dim i As Integer
For i = 1 To 50
If Cells(i, 1).Value = ComboBox1.Value Then
With ListBox1
.ColumnCount = 3
.AddItem Cells(i, 1)
End With
End If
Next i
End Sub

このときコンボボックスと同じ値の行について、
リストボックスにA列、D列、G列を表示させるにはどうしたらいいのでしょうか。
例えばコンボボックスで「りんご」を選択したときに、
リストボックスを
りんご　赤　120円
りんご　赤　160円
と表示させたいのですが、
.AddItem Cells(i, 1)
では一列だけしか表示できません。
Rowsorceを使ってみたりしましたが、どうにもうまく出来ませんでした。
よろしくお願いいたします。

回答No.3

onlyromOK · ベストアンサー

回答２、onlyromOKです。
回答２の、ListBox1_Changeイベントは間違いではありませんが、
以下のようにすると
ListBoxの行をカウントするための変数ListRowが不要になります。

'---------------------------------------
Private Sub ComboBox1_Change()
　Dim R As Long

　ListBox1.Clear

　For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　If Cells(R, "A").Value = ComboBox1.Value Then
　　　ListBox1.AddItem Cells(R, "A").Value
　　　ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(R, "D").Value
　　　ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(R, "G").Value
　　End If
　Next R

End Sub
'----------------------------------------------

また、With ListBox1とWithステートメントを使うとコードが見易くなります。

お礼

ありがとうございました。やりたいことがずばり出来ました！
　ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(R, "D").Value
のコードを知らなかったので勉強になりました。
またよろしくお願いいたします。

回答No.2

onlyromOK

Listプロパティを使用するといいでしょう。

'-------------------------------------------
Private Sub UserForm_Initialize()
　With ComboBox1
　　　.Clear
　　　.AddItem "aaa02"
　　　.AddItem "aaa03"
　　　.AddItem "aaa04"
　End With

　With ListBox1
　　.Clear
　　.BoundColumn = 1
　　.ColumnCount = 3
　End With
End Sub

'---------------------------------------
Private Sub ComboBox1_Change()
　Dim R As Long
　Dim ListRow As Integer

　ListBox1.Clear

　For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
　　　If Cells(R, "A").Value = ComboBox1.Value Then
　　　　　ListBox1.AddItem Cells(R, "A").Value
　　　　　ListBox1.List(ListRow, 1) = Cells(R, "D").Value
　　　　　ListBox1.List(ListRow, 2) = Cells(R, "G").Value
　　　　　ListRow = ListRow + 1
　　　End If
　Next R
End Sub
'-------------------------------------------

質問では、For i=1 to 50　と最終行が５０に固定されてますが、
最終行は自動取得した方がいいでしょう。
また、質問には、ListBoxのBoundColumnメソッドがありませんが
それもいれておかないと。
そしてまた、ListBoxに値をセットする前には必ずListBox1.Clearが必須。

回答No.1

hige_082

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

UserForm1.ListBox1.RowSource = "Sheet1!A1:G50"
UserForm1.ListBox1.ColumnCount = 7
UserForm1.ListBox1.ColumnWidths = "30,0,0,30,0,0,30"

詳細はヘルプをよく読んでください