Listbox Rowsource...

[m0atz]

Hi all,

just a quick one regarding the rowsource of a multicolumn listbox in my userform.

i want the rowsource to show only the cells autofiltered in my "Data" column. Col A:C is where my 'table' of info sits, and i filter out values in Col A, how do i get the rowsource to reflect only these values?

Cheers

 

[Andrew Poulsom]

You would have to use AddItem or List instead of RowSource.

 

[m0atz]

sorry - i dont understand..?

 

[Andrew Poulsom]

You would need to loop through the cells in the range, test their visibility and add them to the ListBox if True. You could do that in the UserForm's Initialize event procedure.

 

[m0atz]

ADVERTISEMENT

If cannabalised other posts I've found to come up with the following code: I just dont seem to be able to get 3 columns displayed - not sure which bit to tweak to achieve that...do you have any ideas?

Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim RngF As Range
Dim RngV As Range
Dim myCell As Range
Set wks = Worksheets("Sheet1")
With wks
 
    Set RngF = .AutoFilter.Range
 
    Me.ListBox1.RowSource = ""
 
            If RngF.Columns(1).Cells.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
            MsgBox ("No customer selected")
            Else
 
                With RngF
                    Set RngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
                End With
 
                    For Each myCell In RngV.Cells
                        Me.ListBox1.AddItem myCell.Offset(0, 1).Value
                    Next myCell
            End If
 
End With
End Sub

 

[Kenneth Hobson]

You will need to use Columns or add by List(x, y).

Post a sample xls to a free shared site like 4shared.com if needed.

 

[Andrew Poulsom]

Instead of:

Me.ListBox1.AddItem myCell.Offset(0, 1).Value

try:

With Me.ListBox1
    .AddItem myCell.Offset(0, 1).Value
    .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
    .List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
End With

 

[m0atz]

Spot on - problem solved - thanks so much for the time and effort - appreciated.

colin