PDA

View Full Version : [SOLVED] VBA to populate a multicolumn listBox using Additem



simora
10-06-2014, 06:00 PM
I'm doing a search like this;


Set rngFind = .Find(strValueToPick, LookIn:=xlValues, LookAt:=xlWhole)
strFirstAddress = rngFind.Address
Set rngPicked = rngFind


From this I want to populate the listBox using 3 columns.

I can get the first 2 columns, but how do I get the 3rd column; that on the right to show the month derived from the address.

This is the relavant code with the notes etc..

a = Split(rngFind.Address, "$")

loc = (a(2))
e = Cells(loc, 1) 'Gives the Company Name in Col A
Col = rngFind.Column

' MsgBox Col but as a number

'MsgBox e 'Gives the Company Name in Col A
'MsgBox loc 'Gives the Row Number

How do I get this part to show that 3rd column



With ListBox1
.AddItem rngFind.Address ' Cell Address
.List(.ListCount - 1, 1) = (Cells(loc, 1))


The above shows 2 columns

I tried


.List(.ListCount 0, 1) = (Cells(1, loc))
.List(.ListCount-1, 2) = (Cells(1, loc))


Amoung other things

Bob Phillips
10-07-2014, 12:42 AM
This all seems very confused. You use ListCount -1 the ListCount 0 (whatever that means), Cells(loc, 1) then Cells(1, loc).

It seems to me that all you need


.List(.ListCount-1, 1) = value 2
.List(.ListCount-1, 2) = value 3

snb
10-07-2014, 02:45 AM
If a Listbox/combobox has 3 columns the method .additem only adds an empty array to the listbox/combobox
After that you will have to fill that empty array.
Besides, .AddItem isn't my favourite method to populate a Listbox/Combobox. Using the property .List is.

See also: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

simora
10-07-2014, 05:41 AM
Thanks xld (http://www.vbaexpress.com/forum/member.php?2139-xld) :

This was the workable solution

With ListBox1
.AddItem rngFind.Address ' Cell Address
.List(.ListCount - 1, 1) = (Cells(loc, 1)) ' First Column of found row
.List(.ListCount - 1, 2) = (Cells(1, Col)) ' Header row of found column

End With


snb (http://www.vbaexpress.com/forum/member.php?44644-snb)

I agree 100% with you. .AddItem gets messy, & thanks for the link.

snb
10-07-2014, 08:00 AM
sn=columns(1).find("xxx",,,1).resize(,2)

With ListBox1
.AddItem
.List(ubound(.List), 1) = sn(1,1)
.List(ubound(.List), 2) = sn(1,2)
End With