Populate ComboBox with contents of ListBox

Hi VBAX

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

Ive tried this:

Code:

---

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!!

Code:

---


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

---

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:

Code:

---

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

---

Apologies,
It should be

Code:

---


For i = 0 To ListBox1.ListCount - 1

---

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.

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

Code:

---

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

---

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.

Simpler:

Code:

---


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

---

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:

Code:

---

    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?

Maybe helps!

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

Code:

---


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

---

How about

Code:

---

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

---

Quote:

---

Originally Posted by mikerickson

How about

Code:

---

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

True
:blush

I should finish my coffee before posting.

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:

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

Quote:

---

Originally Posted by mdmackillop

Code:

---


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.

Code:

---

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

---

Please post a copy of your workbook.

Quote:

---

Originally Posted by mdmackillop

Please post a copy of your workbook.

---

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

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

Quote:

---

Originally Posted by mdmackillop

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

I don't have time to try to work out what you are trying to achieve here. If you want to populate an ActiveX combo from a Userform List, why not just write the selected items to a dynamic range and load the combo from that as the RowSource?

Quote:

---

Originally Posted by asystole0

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

---

Till this amendment we all were so far from essence of the question either as ComboBox is far from KlitschkoBox :)

So, for referencing to the property/method of ActiveX control embedded into the sheet, just insert Object keyword between control's code name and its property/method, like this:
Sheet5.ComboBox15.Object.List = ...

This works for UserForm_Initialize code of RatesPuller form of your example:

Code:

---


' Code of RatesPuller userform
Private Sub UserForm_Initialize()
    ' Next line is for debugging only, in real task call FillList() for populating of the userform's ListBox5
    ListBox5.List = Array("SainsLabour", "SainsLabourPrice", "SainsMaterials", "SainsMaterialPrices", "SainsEquipment", "SainsEquipmentPrices")
    ' Copy list of RatesPuller form's ListBox5 to the ComboBox15 embedded into the sheet Contract
    Sheets("Contract").ComboBox15.Object.List = ListBox5.List
End Sub

---

Regards,
Vladimir

Quote:

---

Originally Posted by ZVI

Till this amendment we all were so far from essence of the question either as ComboBox is far from KlitschkoBox :)

So, for referencing to the property/method of ActiveX control embedded into the sheet, just insert Object keyword between control's code name and its property/method, like this:
Sheet5.ComboBox15.Object.List = ...

This works for UserForm_Initialize code of RatesPuller form of your example:

Code:

---


' Code of RatesPuller userform
Private Sub UserForm_Initialize()
    ' Next line is for debugging only, in real task call FillList() for populating of the userform's ListBox5
    ListBox5.List = Array("SainsLabour", "SainsLabourPrice", "SainsMaterials", "SainsMaterialPrices", "SainsEquipment", "SainsEquipmentPrices")
    ' Copy list of RatesPuller form's ListBox5 to the ComboBox15 embedded into the sheet Contract
    Sheets("Contract").ComboBox15.Object.List = ListBox5.List
End Sub
 

---

Regards,
Vladimir

---

Thanks Vladmir, I can see how that works, I dont think i was that for off!!!

Now I get "runutime error '70' - permission denied", Do I need to make the comboBox editable before trying to fill it with the contents from the list box?

That code worked for me. Is your sheet protected, or does your combo have a row source property already set?

Quote:

---

Originally Posted by asystole0

Now I get "runutime error '70' - permission denied", Do I need to make the comboBox editable before trying to fill it with the contents from the list box?

---

The questions:
1. The code of post #22 being copied into your example of post#18 is working without error, isn’t it? - Please confirm.
2. So, what is the problematic code?
3. Which line of the code debugger highlights with error messaging?
4. What do you mean saying "make the comboBox editable "?