Adding multiple comboboxes to a sheet each linking to a different cell

[afpPaul]

I have added an activeX combo box to my sheet and linked it to cell Y3, my ListFillRange is "FullList" - works fine. I would like to repeat this for each cell in this range -Y3:AG218 doing this one at a time would take very long. Is there an easier way of doing this? Any help would be great!

[rylo]

Hi

Do you want the macro to create the comboboxes as well (over 1900 of them?). If so, then where do you want the comboboxes to go? Over the relevant cell location?

Seems extreme in the least. Why not use data validation for the cells in question?

rylo

[afpPaul]

I would like the comboboxes to be placed in the centre of each cell in the range -Y3:AG218. Also the comboboxes are to be linked to each of these cells. Data Validation is what i am using currently, but because of its limitations the biggest being no auto fill and the 8 row limit i have been asked to use an activeX type combobox.
Hope this can be done. Thanks for the reply rylo, any help on this would be greatly appreciated.

[rylo]

Hi

OK, try this.

Please Login or Register  to view this content.

rylo

[afpPaul]

Hi
thanks for getting back.
almost but not quite. Got runtime error at Y137. Tried it twice. Also it is removing all cell formate any way i can keep the existing formate in place.
thanks again.

[afpPaul]

This is the error im getting

Automation Error
Element not found

Also when i try to compile the VBA i get - object library invalid or contains reference to object definitions that could not be found

Hope that helps

[rylo]

Hi

The bit of testing I did it did not change the formatting but you couldn't see any formatting as the combo box covered the entire cell. Didn't have any errors.

Try building a new spreadsheet with some formatting in the relevant range, set up the named range, and see if it has any problems there. If it doesn't then see what differences there are between the 2 sheets. Do you have any merged cells in the range? Does that cause problems?

If all else fails, then set up an example file that does produce the same errors, and attach so we can review.

rylo

[afpPaul]

Hi
I also have been testing with your code. I changed the range to Y3:Y218 and the Height to 18.75, this works fine. as i expand the range one column at a time i get the error in column AE i think there some merged cells. i can work around this no problem. I'm now wondering if we can set the position to be centred verticality within the cells. this has saved me allot of time, Thank you so much.

[rylo]

Hi

if you are fixing the height of the comboboxes, how does this compare to the size of the cell? There isn't any direct connection between the cell and the combobox in terms of positioning, so I don't think you can just do a "centre vertically". You would need to determine the size of the cell, and make the combobox smaller, and position it in relation to the edges.

Again, if you have a specific formatting situation, the best way is to add an example file that reflects your real situation so we have something concrete to work with.

rylo