Adding items to Combobox

[randheerp]

Hi All,

In my excel sheet, from B2 to B20 i have datas like,

aaa
bbb
ccc
aaa
ccc
ddd
rrr
ttt
bbb
rrr
ttt

I want to add this to my combobox1 with out repeation as, 'aaa' should be listed in combobox once. What is the macro for this or we can do it in Combobox properties itself?

[Bob Phillips]

Please Login or Register  to view this content.

[randheerp]

Hi Bob,

I am new to macro.

I am having the below values in A column of sheet1;
aaa
bbb
ccc
aaa
ccc
ddd
rrr
ttt

If i have a combobox1 in Sheet1, what is the script to be included to add the unique values to combo box, is it 'Sheets(1).ComboBox1.AddItem ary(NextItem)'?

In the script you said, where i have to insert it?

[royUK]

What kind of combobox re you suing , which menu did you insert from?

[randheerp]

I am using combobox from Control Toolbox.

I am usng MS Office 2003.

[haissk]

here is the code

Please Login or Register  to view this content.

put this in worksheet.activate event

[randheerp]

Hi Haissk,

Thanks a lot Boss!

[randheerp]

Hi,

The script you given was working fine when data was like,
aaa
bbb
ccc
aaa
bbb
In combobox 'aaa' is gettng added only once, fine.

But when i have data like,
111
222
333
222
111
For this when i use the same script datas in the column is getting repeated in combobox also, any idea?

[Bob Phillips]

This works fine

Please Login or Register  to view this content.

[randheerp]

Hi Bob,

In your script, you have not written the command to add those values to Combobox.

Could you edit the script of yours adding the above thing?

[Leith Ross]

Hello randheerp,

The combo box is loaded by this line in Bob's code...

Please Login or Register  to view this content.

If the name of your combo box isn't ComboBox1, you will need to change the name to match your combo box's name.

[randheerp]

Thanks Mr. Bobs and also Ross...

Actually i didn't notice the last line of Bob's script coz it was under the scroll bar.

ThankU Bob once again.

[randheerp]

Hi,

My datas in A column is like,

11
22
33

44
55

I am having a row gap between 33 and 44.

When i execute the below mentioned script, i my combobox i am getting up to 33. So that gap is causing problem.

Please Login or Register  to view this content.

What modification to be made on the above script to overcome the above mentioned issue?