Listbox value null

[miami2k]

Dear All,

I have this simple code copied from somewhere else but I can' t check if it works because it stops immediately:

Private Sub CommandButton2_Click()


With Me.ListBox1
.ColumnCount = rRng.Columns.Count
.ColumnWidths = "0,40,50,60,60,0,0,0,0,0,0,0,0,0,0,150,120,100,65,70,50"
.MultiSelect = fmMultiSelectMulti
.ListIndex = -1
End With
With Rng
For i = 1 To .Rows.Count
If .Cells(i, 2) = 2 Then
ListBox2.AddItem .Cells(i, 1).Value
For j = 2 To .Columns.Count
ListBox2.List(ListBox2.ListCount - 1, j - 1) = .Cells(i, j).Value
Next j
End If
Next i
End With


End Sub

Runtime error 424 and Me.ListBox1 returns Null as value, it looks like the listbox is not activated.

How can I solve it?

Apart from that is 1 day that I'm going crazy trying to find a simple code to fill the listbox filtering the rows depending on values of one column.
I undestand that I have 2 options: one is to filter the values of the origin and show only all the ones remaining or the other option is to check line by line the values and add each line.
Do you have any suggestion for a simple solution?


Thank you!

 

[tlowry]

Well that got complicated in a hurry. How about:

Private Sub CommandButton2_Click()
    RillLbWithRange Me.ListBox1, Range("A1:B3")
End Sub
Sub RillLbWithRange(lb, rng)
    With lb
        .ColumnCount = rng.Columns.Count
        .MultiSelect = fmMultiSelectMulti
        .ListIndex = -1
        .ListFillRange = rng.Address
    End With
End Sub

You will need to do something with the column widths in ListBox1.

PPR - Please Post Results.

 

[miami2k]

No luck.

Error 438 on line: .ListFillRange = rng.Addresslb still null

 

[MickG]

if your List box is in a Userform then you need "Rowsource":-

.RowSource = rng.Address

You could also use:-

.List = rng.Value

First ensuring there is no Address in the Listbox Code module Properties for the Rowsource/listFillRange

 

[miami2k]

Where do I put this?
I've also tried to replace "With me.ListBox1" with "With UserForm2.ListBox1" but when I run the code the lb is always null. Error 424 Object required!
It looks the same when a sheet is not activated!

Any other working code is accepted.

 

[Fluff]

Do you have a listbox on userform2 called ListBox1?