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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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