Solved: Adding select rows to a listbox in a form

[ProteanBeing]

What I want to do is use autofilter to limit the list then only add the filtered rows to the listbox. Is this possible?

[rlv]

[vba]

Sub OnlyFiltered()
Dim MyCell as Range

For each MyCell in ActiveSheet.Range("A1:A100")
If not MyCell.EntireRow.Hidden then

''' Do a bunch of stuff

End If
Next MyCell


[/vba]

[ProteanBeing]

It's the "Do a bunch of stuff" I need help with. How do I enter the data (multi-columns) into the list?

[rlv]

Create a userform "Userform1" and put a list box on it , "Listbox1"


[vba]Sub LoadSomeUserFormListBox()
Dim ListArr(1 To 2, 1 To 2) As String

ListArr(1, 1) = "Elem 1,1"
ListArr(1, 2) = "Elem 1,2"
ListArr(2, 1) = "Elem 2,1"
ListArr(2, 2) = "Elem 2,2"

UserForm1.ListBox1.List() = ListArr

' Show the UserForm
UserForm1.Show
End Sub
[/vba]

[mikerickson]

This worked for me.

Private Sub UserForm_Activate()
Dim onecell As Range
With Me.ListBox1
    .columnscount = 5
    For Each onecell In ThisWorkbook.Sheets("sheet1").Range("A1:a10").SpecialCells(xlCellTypeVisible)
        .AddItem onecell.Value
        .List(.ListCount - 1, 1) = onecell.Range("b1").Value
        .List(.ListCount - 1, 2) = onecell.Range("c1").Value
        .List(.ListCount - 1, 3) = onecell.Range("d1").Value
        .List(.ListCount - 1, 4) = onecell.Range("e1").Value
    Next onecell
End With
End Sub

(If you have more than 10 columns, this technique will not work.)

The trick is that ListBox1.List is zero based, so when the .ColumnCount is 5, the highest column index is 4. Hence, the -1 in the .ListCount-1.

[ProteanBeing]

This worked good. However I want a header in this list. Is that possible?

[mikerickson]

Not on my Mac.
A Windows user would have to speak to that situation.

[ProteanBeing]

I just put labels above the list. Thanks for your assistance!!