Filtering data to a userform listbox

[Kryer]

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 A11,
data range is A2200.
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("A2200"), i have
tried Listbox1.rowsource("A2200"), 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.

 

[Mel Arquiza]

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.