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?
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?
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!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.