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]

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

[/vba]

[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
[VBA]
For i = 0 To ListBox1.ListCount - 1

[/VBA]

[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:

[VBA]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[/VBA]

[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:

[VBA]
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
[/VBA]

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
[vba]
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

[/vba]

[mikerickson]

How about
[VBA]Private Sub CommandButton1_Click()
ComboBox1.List = ListBox1.List
End Sub[/VBA]

[ZVI]

How about
[VBA]Private Sub CommandButton1_Click()
ComboBox1.List = ListBox1.List
End Sub[/VBA]

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 Luistbox2
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]

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

[/vba]

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".