Populating a listbox after making a selection from a combo

jaffacakeking

Board Regular
Joined
Jan 13, 2002
Messages
149
Hi everyone,

i'm using the following code to populate a combobox on a userform;
ComboBox1.RowSource = vbNullString
ComboBox1.RowSource = Range("A1", Range("A65536").End(xlUp)).Address

This works well but i'm having trouble polulating a listbox once the selection has been made. I'd like to have the adjacet cell's text placed in the listbox i.e. if the user selects the text in A2 then i'd like the text from B2 to be added to the list.

I've got an idea in my head but i'm not sure how to phrase it, kind of like...

If combobox1.text = "x" then listbox1.Range("A1 +1", Range("A65536").End(xlUp)).Address.additem

I know i need to refer to either the text or value of the adjacent cell but thats when i get lost.

Thanks for any help!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Re: Populating a listbox after making a selection from a com

jaffacakeking said:
Hi everyone,

i'm using the following code to populate a combobox on a userform;
ComboBox1.RowSource = vbNullString
ComboBox1.RowSource = Range("A1", Range("A65536").End(xlUp)).Address

This works well but i'm having trouble polulating a listbox once the selection has been made. I'd like to have the adjacet cell's text placed in the listbox i.e. if the user selects the text in A2 then i'd like the text from B2 to be added to the list.

I've got an idea in my head but i'm not sure how to phrase it, kind of like...

If combobox1.text = "x" then listbox1.Range("A1 +1", Range("A65536").End(xlUp)).Address.additem

I know i need to refer to either the text or value of the adjacent cell but thats when i get lost.

Thanks for any help!

If think you idea is correct, but your syntax is wrong

If combobox1.text = "x" then listbox1.additem Range("A1 +1", Range("A65536").End(xlUp)).Address

Should be what you want.
 

jaffacakeking

Board Regular
Joined
Jan 13, 2002
Messages
149
Re: Populating a listbox after making a selection from a com

Cheers Cbrine,

That worked but my original but my original post was poorly phrased :oops:

The combobox contains a list of names and i wanted to populate the listbox on the userform with the text, associated with the name, in the adjacent cell.

I don't want to use an 'if' arguement but i don't know how to use an index search of my second range to populate my listbox.

Hope thats a little clearer!

Thnaks in adavnce!
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Re: Populating a listbox after making a selection from a com

jaffacakeking said:
Cheers Cbrine,

That worked but my original but my original post was poorly phrased :oops:

The combobox contains a list of names and i wanted to populate the listbox on the userform with the text, associated with the name, in the adjacent cell.

I don't want to use an 'if' arguement but i don't know how to use an index search of my second range to populate my listbox.

Hope thats a little clearer!

Thnaks in adavnce!

I'm not sure how to get what you want without the if, but with the if

If combobox1.text = "x" then listbox1.additem Range(range("A1").offset(0,1), Range("A65536").End(xlUp).offset(0,1)).Address

This should add the entries in Column B(Adjusted to the size of your column A) to your listbox.

Hope this helps. I haven't tested the code, so you may need to tweak.
 

jaffacakeking

Board Regular
Joined
Jan 13, 2002
Messages
149
Re: Populating a listbox after making a selection from a com

Hi,

Thanks for the continuing help!

Using the code you supplied i get the cell range rather that the cell's text or value... Where abouts in the code do i refer to the cells contents?

Also, as my list contians a lot of names and the list will continue to grow i was hoping not to have to specifically type an arguemnt for each name. Anyone got another way of achieveing this?

Thanks again!
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Re: Populating a listbox after making a selection from a com

Cbrine said:
jaffacakeking said:
Cheers Cbrine,

That worked but my original but my original post was poorly phrased :oops:

The combobox contains a list of names and i wanted to populate the listbox on the userform with the text, associated with the name, in the adjacent cell.

I don't want to use an 'if' arguement but i don't know how to use an index search of my second range to populate my listbox.

Hope thats a little clearer!

Thnaks in adavnce!

I'm not sure how to get what you want without the if, but with the if

If combobox1.text = "x" then listbox1.additem Range(range("A1").offset(0,1), Range("A65536").End(xlUp).offset(0,1)).Address

This should add the entries in Column B(Adjusted to the size of your column A) to your listbox.

Hope this helps. I haven't tested the code, so you may need to tweak.
Sorry, I copied that from your orginal code. Just replace the Address at the end of the statement with
.value.

If combobox1.text = "x" then listbox1.additem Range(range("A1").offset(0,1), Range("A65536").End(xlUp).offset(0,1)).value
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top