Populate ComboBox with contents of ListBox

[asystole0]

Hi VBAX

I need to populate a ComboBox with the contents of the ListBox.

Ive tried this:

Me.ComboBox1.ListFillRange = ListBox5

It doenst throw any errors but nothing shows on the list box, the form isnt open with the lists on so im not sure if thats where the problem is? Any help would be appreciated!!

[mdmackillop]

 
For i = 0 To ListBox5.ListCount - 1
    ComboBox1.AddItem ListBox5.List(i)
Next

[aplfalcon]

I'm gonna piggypack this one

I am working on a similar problem and I tried this solution but I'm getting the error "Run-time error '381': Could not get the List property. Invalid property array index."

What it does is when I click the "add" button that transfers the selected item in ListBox1 to ListBox2, it is currently transferring the entire list in ListBox1 to ListBox2. Here is my code:

Private Sub List1AddButton_Click()
    Dim i As Variant
    For i = 0 To ListBox1.ListCount
        ListBox2.AddItem ListBox1.List(i)
    Next i
End Sub

[mdmackillop]

Apologies,
It should be

 
For i = 0 To ListBox1.ListCount - 1

[Shortz]

I'm going to jump on this too as its kind of related.

what is the best way to retrieve the selected values in a listbox to then use those values to search and destroy rows or columns in the workbook?

Edit: Can someone also please point me to a good Listbox tutorial? i've tried googling but all it does is tell me how to put stuff in to a listbox not deal with the selected values.

[aplfalcon]

I just figured it out. Here's how I do it:

Private Sub List1AddButton_Click()
    Dim i As Variant
    If ListBox1.ListIndex = -1 Then Exit Sub
    For i = 0 To ListBox2.ListCount - 1
        If ListBox1.Value = List2.List(i) Then
            Exit Sub
        End If
   Next i
    ListBox2.AddItem ListBox1.List(i)
End Sub

[aplfalcon]

Actually I'm still interested in Shortz's question so I'll keep an eye out.

By the way, for some reason, Excel 2007 always wants me to define "i" -- it's probably something i'm doing wrong.

[ZVI]

Simpler:

Private Sub CommandButton1_Click()
     ComboBox1.List = ListBox1.List
End Sub

[aplfalcon]

Okay, so I realized today that the code I worked out doesn't work for what I need to do. I need to be able to copy the selected item from Box 1 to Box 2. I want the user to be able to select multiple items at one time. Right now, if I try to call the .Value property of the listbox while it's MultiSelect property is set to fmMultiSelectMulti, it returns errors. It only works if I set it to single.

Here's what I have right now:

    Dim i As Variant
    If TListBox2.ListIndex = -1 Then Exit Sub
    For i = 0 To List2.ListCount - 1
    If TListBox2.Value = List2.List(i) Then
        Beep
        Exit Sub
    End If
    Next i
    List2.AddItem TListBox2.Value

I tried to replace "value" with Selected(0) but it doesn't seem to work... Any ideas?

[omnibuster]

Maybe helps!

[mdmackillop]

Using Omnibuster's form, we can skip the intermediate list

 
Option Explicit

Private Sub UserForm_Activate()
    Dim sh As Worksheet
    Set sh = Sheets(1)
    With sh
        ListBox1.List() = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value
    End With
End Sub
 
Private Sub CommandButton4_Click()
    Dim i As Long
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then ComboBox1.AddItem .List(i)
        Next
    End With
End Sub

[mikerickson]

How about

Private Sub CommandButton1_Click()
        ComboBox1.List = ListBox1.List
End Sub

[ZVI]

How about

Private Sub CommandButton1_Click()
    ComboBox1.List = ListBox1.List
End Sub

Hi Mike,
The same was offered in post #8, but seems that it has not helped - the task was changed a bit
Vladimir

[mikerickson]

True


I should finish my coffee before posting.

[ZVI]

After rereading of this thread with a cup of coffee it is my understanding of the modified task: the multi-selected items of ListBox1 have to be added into ListBox2.
And I guess that Listbox2 list shall be unique and sorted, because without it the example is present in VBA-help.
If so then example is attached. It’s UserForm1 code:

' Copy all the code into UserForm with ListBox1, Lisbox2 and CommandButton1
' Put some test items into ListBox1

Private Sub UserForm_Initialize()
     ListBox1.List = Array("Item1", "Item2", "Item3", "Item4", "Item5")
End Sub

' Copy selected items of Listbox1 into Listbox2
Private Sub CommandButton1_Click()
     Lb1SelectedToLb2 ListBox1, ListBox2
End Sub

' Copy selected items of Lb1 into Lb2, make it unique, sort and place into Lb2
Sub Lb1SelectedToLb2(Lb1 As MSForms.ListBox, Lb2 As MSForms.ListBox)
   Dim a(), i&, j&, s$
   On Error Resume Next
   With New Collection
       ' Sort unique items of Lb2
       For j = 0 To Lb2.ListCount - 1
           s = Trim(Lb2.List(j))
           If IsEmpty(.Item(s)) Then
               ' Thanks to PGC01 for suggested fast sorting method for collection!
               For i = 1 To .Count
                   If s < .Item(i) Then Exit For
               Next
               If i > .Count Then .Add s, s Else .Add s, s, Before:=i
              End If
          Next
          ' Add as unique the selected items of Lb1 into Lb2
          For j = 0 To Lb1.ListCount - 1
              If Lb1.Selected(j) Then
                   s = Trim(Lb1.List(j))
                  If IsEmpty(.Item(s)) Then
                      For i = 1 To .Count
                           If s < .Item(i) Then Exit For
                      Next
                      If i > .Count Then .Add s, s Else .Add s, s, Before:=i
                      End If
                      Lb1.Selected(j) = False
                 End If
             Next
             ' Copy Collection items into array
            ReDim a(1 To .Count)
            For i = 1 To .Count
                a(i) = .Item(i)
            Next
        End With
        ' Put unique sorting items into Lb2
       Lb2.List = a()
End Sub

Vladimir

[asystole0]

 
For i = 0 To ListBox5.ListCount - 1
    ComboBox1.AddItem ListBox5.List(i)
Next

Thanks mdmackillop, I tried suggest code but I get runtime 424 "object required".

Ive tried manipulating most of the ways above but none work.

Dim i As Variant
For i = 0 To ListBox5.ListCount - 1
    ComboBox15.AddItem ListBox5.List(i)
Next i

[mdmackillop]

Please post a copy of your workbook.

[asystole0]

Please post a copy of your workbook.

Workbook attached. Stripped version of my original doc but effectively the same. Code is in Form "CustSelector"

[mdmackillop]

The basic problems appears to be that your form contains no comboboxes.

[asystole0]

The basic problems appears to be that your form contains no comboboxes.

ahhh, Probably my fault for not explaining properly, the ComboBox is embeded onto the sheet "Contract".