Listbox with Multiple Columns

[Eddie_SP]

Hi !

I have one Listbox in my Form, and if the value of "Column A" is the same of
the Combobox of the Form, it must be shown on List box the Column "B" and "C"
values...

But the value of Column "A" can be repeated below in other rows...

I have the following, but it doesn't work:



Dim ComboRef As String
Dim i As Integer
Dim RNG As Range

ComboRef = Me.ComboBox1.Value

i = 0

Worksheets(6).Activate
While (ActiveSheet.Cells(1 + i, 2) <> 0)
i = i + 1
If Cells(1 + i, 1) = ComboRef Then
RNG = Range(Cells(1 + i, 2), Cells(1 + i, 3))
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 2
.ColumnHeads = True
.TextColumn = True
.ListIndex = i
.AddItem RNG
End With
End If
Wend


Please someone help me...

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub ComboBox1_Change()

Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

If Me.ComboBox1.ListIndex < 0 Then
'nothing selected
Beep
Exit Sub
End If

'I wouldn't rely on the postion of the worksheet (6).
'I'd use its name (or its codename)
Set wks = Worksheets("Sheet1")

With wks
'headers in row 1 of that worksheet???
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
With Me.ListBox1
.AddItem myCell.Offset(0, 1).Value 'column B
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value 'column C
End With
End If
Next myCell

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
'some test data
With Me.ComboBox1
.AddItem "A1"
.AddItem "A2"
.AddItem "A3"
.AddItem "A4"
End With

With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "100;100"
.MultiSelect = fmMultiSelectSingle
End With
End Sub

 

[Eddie_SP]

Hi Dave !!! Worked 90% !!! =)

But if I choose another value on Combobox1, in the Listbox1, the old values,
they stay there, do you know how do I "clear" those values?

I tried before "For Each MyCell" the command like:

Me.Listbox1 = Clear

But it didn't work !


Dave, thank you man, as always !!!


Eddie.

 

[Dave Peterson]

Try...

End With

Me.ListBox1.Clear '<-- added

For Each myCell In myRng.Cells

 

[Eddie_SP]

It did woooork !!! =)

\o/

Thank you Dave !!!

Very very much ! =)