VBA Populate a list box with a range of cells without using a named range

[PolarStar]

Hello,

I have a vba user form with 1 combo box and 2 list boxes. Once the user makes a selection in ComboBox1 I want a corresponding set of cell values to populate the ListBox1. The user will then select one or more items from the ListBox1 which will get moved to ListBox2. Once moved to ListBox2 I want the items to be cleared (removed) from ListBox1 to help the user select more items if necessary.

For Example:
The user selects Colorado from a list of StateNames in ComboBox1, base on that selection ListBox1 is populated with all the counties in Colorado. The user then selects one or more counties from that list and moves them (with command button) to ListBox2. Once moved the selected items are removed from ListBox1 so they cannot accidentally be selected again and to make it easier to see remaining items in the list.

Initially I used a named range in the list box row source property, but have learned that you cannot clear selected items from a named range. What is another way to populate a list box with cell values in a specific column?

My data looks like this, but would have all 50 states and all counties listed:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Colorado[/TD]
[TD="align: center"]Illinois[/TD]
[TD="align: center"]Arizona[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]San Juan[/TD]
[TD="align: center"]DuPage
[/TD]
[TD="align: center"]Navajo[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Quray[/TD]
[TD="align: center"]DeKalb[/TD]
[TD="align: center"]Apache[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Gunnison[/TD]
[TD="align: center"]Kane[/TD]
[TD="align: center"]Cochise[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Pitkin[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Coconino[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Custer[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yuma[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Pueblo[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Las Animas[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Try this:
This assumes all you State name are in row(1) column 1 to 50
And the counties are in the row(2) and below for each county in that state.

And your Listbox1 must be set for Multiselect in the properties window

And you have 1 combobox and Two Listboxes

And one command Button

You will see how I named my controls.
I use default names.

When you select a State from Combobox1
All the counties for that State will be loaded into Listbox1
When you select the counties in Listbox two you want added to Listbox2
You then need to click Command button1

The values selected in Listbox1 will be copied to Listbox2 and removed from Listbox1

I believe this is what you wanted.

You need to put these three scripts in your Userform

Private Sub ComboBox1_Change()
'Modified  10/12/2018  1:15:36 AM  EDT
ListBox2.Clear
Dim c As Long
Dim Lastrow As Long
Set SearchRange = Cells(1, 1).Resize(, 50).Find(ComboBox1.Value)
c = SearchRange.Column
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
ListBox1.Clear
    For i = 2 To Lastrow
        ListBox1.AddItem Cells(i, c).Value
    Next
End Sub
Private Sub CommandButton2_Click()
'Modified  10/12/2018  1:15:36 AM  EDT
Dim i As Long
Dim b As Long
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        ListBox2.AddItem ListBox1.List(i)
    End If
Next i
For b = ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(b) = True Then
        ListBox1.RemoveItem (b)
    End If
Next b
End Sub
Private Sub UserForm_Initialize()
'Modified  10/12/2018  1:15:36 AM  EDT
Dim i As Long
    For i = 1 To 50
        ComboBox1.AddItem Cells(1, i).Value
    Next
End Sub

 

[PolarStar]

Thanks for your response. It worked great although I did have to Dim i and SearchRange. I am new to VBA but was able to figure it out. Thanks again. I appreciate or taking time to help.

 

[My Aswer Is This]

Thanks for your response. It worked great although I did have to Dim i and SearchRange. I am new to VBA but was able to figure it out. Thanks again. I appreciate or taking time to help.

Yes I forget some times to Dim all things.
Strange it works for me without Dimming but not others.
I need to start remembering that more.

Glad it worked for you.
I was wondering if you would come back and say if it worked.
It's always nice to hear from Posters if things worked for them.
Take care

I even did a search on the net and found listings of all counties in all states and adding them to a few state columns just as a test.