PDA

View Full Version : Solved: Adding select rows to a listbox in a form



ProteanBeing
01-11-2008, 08:11 PM
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
01-11-2008, 09:20 PM
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

ProteanBeing
01-14-2008, 08:00 PM
It's the "Do a bunch of stuff" I need help with. How do I enter the data (multi-columns) into the list?

rlv
01-14-2008, 09:21 PM
Create a userform "Userform1" and put a list box on it , "Listbox1"


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

mikerickson
01-14-2008, 09:34 PM
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
01-15-2008, 07:06 AM
This worked good. However I want a header in this list. Is that possible?

mikerickson
01-15-2008, 07:16 AM
Not on my Mac.
A Windows user would have to speak to that situation.

ProteanBeing
01-15-2008, 07:39 AM
I just put labels above the list. Thanks for your assistance!!