Post: Autofilter and Listbox how to acomplish?
-----------
I used the listbox from the control toolbox toolbar (ActiveX controls) and
put it on sheet1. Then I used this code behind the worksheet.
Option Explicit
Private Sub Worksheet_Activate()
Dim wks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long
Set wks = Worksheets("sheet2")
Set rng = wks.AutoFilter.Range
With rng
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
..Cells.SpecialCells(xlCellTypeVisible)
End With
With Me.ListBox1
..Clear
..ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Value)
For iCtr = 1 To rng.Columns.Count - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End Sub
If you filter the data on sheet2, then go back (and activate sheet1), the
listbox gets updated.
-------
What I would like to know is how to manipulate this onto a UserForm and to
use all the data on a worksheet called "Masters", Column Labels are A1:D1,
data range is A2:D200.
I have a combobox that has a list of names on it and I would like to do a
autofilter type setup based on the name picked out of the combobox to appear
in the listbox for just that person.
I tried to follow this code ( i am somewhat new at this ) and I could not
figure out how to make it fit into a userform situation.

I tried to do a :
Private Sub Combobox1_Change()
If Combobox1.value = "Jeremy" then
Range("a1").select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Combobox1.value
-- after here I have tried to do Listbox1.list = range("A2:D200"), i have
tried Listbox1.rowsource("A2:D200"), etc.. everything always kept coming
back with some sort of error or would list all the cells
with in the listbox.

Does anyone have any ideas for me? I would appreciate the help.

Hi Kryer,

Try this code below all you need is press Alt-F11 create a userform, 2
command buttons, 2 listboxes at the top of the form then paste this code
below.

Private Sub CommandButton1_Click()
If Lb1.ListIndex = 0 Then
Lb2.AddItem Lb1.Text
Lb1.RemoveItem Lb1.ListIndex
End If
End Sub

Private Sub CommandButton2_Click()
Do While Lb1.ListCount 0
Lb2.AddItem Lb1.List(0)
Lb1.RemoveItem (0)
Loop
End Sub

Private Sub UserForm_Activate()
UserForm1.Caption = "Use of Listbox"
CommandButton1.Caption = "Transfer to Listbox 2"
CommandButton2.Caption = "Transfer All"
With Lb1
..AddItem Cells(1, 1).Value
..AddItem Cells(2, 1).Value
..AddItem Cells(3, 1).Value
..AddItem "Darryl"
..AddItem "Dom"
..AddItem "Donna"
..AddItem "Debra"
..AddItem "Dan"
..AddItem "Dieter"
End With
End Sub

Note: Try to put some values for Cells(1,1).Value, Cells(2,1).Value etc.
Hope this helps.

"Kryer" wrote:

Post: Autofilter and Listbox how to acomplish?
-----------
I used the listbox from the control toolbox toolbar (ActiveX controls) and
put it on sheet1. Then I used this code behind the worksheet.
Option Explicit
Private Sub Worksheet_Activate()
Dim wks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long
Set wks = Worksheets("sheet2")
Set rng = wks.AutoFilter.Range
With rng
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
With Me.ListBox1
.Clear
.ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Value)
For iCtr = 1 To rng.Columns.Count - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End Sub
If you filter the data on sheet2, then go back (and activate sheet1), the
listbox gets updated.
-------
What I would like to know is how to manipulate this onto a UserForm and to
use all the data on a worksheet called "Masters", Column Labels are A1:D1,
data range is A2:D200.
I have a combobox that has a list of names on it and I would like to do a
autofilter type setup based on the name picked out of the combobox to appear
in the listbox for just that person.
I tried to follow this code ( i am somewhat new at this ) and I could not
figure out how to make it fit into a userform situation.

I tried to do a :
Private Sub Combobox1_Change()
If Combobox1.value = "Jeremy" then
Range("a1").select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Combobox1.value
-- after here I have tried to do Listbox1.list = range("A2:D200"), i have
tried Listbox1.rowsource("A2:D200"), etc.. everything always kept coming
back with some sort of error or would list all the cells
with in the listbox.

Does anyone have any ideas for me? I would appreciate the help.