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 SubHi Mike,
The same was offered in post #8, but seems that it has not helped - the task was changed a bit :)
Vladimir

[mikerickson]

True
:blush

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]

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

[mdmackillop]

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?

[ZVI]

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 (http://www.klitschko.com/eng/Home.html) :)

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

[asystole0]

Till this amendment we all were so far from essence of the question either as ComboBox is far from KlitschkoBox (http://www.klitschko.com/eng/Home.html) :)

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

[mdmackillop]

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

[ZVI]

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