Copy Excel Combo Box into Every Cell in Column

Question

I have created a combo box in a worksheet following the instructions in another post. Now I'd like to have this box appear in every cell in a column. The idea is that I can keep a running list of what I'm doing (time entries) and use the combo box for each new row. I know I can just manually do it for each row, but is there an easier way, like Excel's "copy down" feature?

Answer 1

VBA combo box duplication

The combo box, or most visible objects has its default "Object positioning" properties set to "Move but don't size with cells". This also applies to copying of a cell or multiple cells. However, if the setting is set to "Don't move or size with cells", the object will not copy along with a copied cell.

Place your combo box wholly inside a cell. Copy that cell (not the object) and then paste in the cell below. The combo box will copy along (if it doesn't, make sure your are in design mode in the developer tab). Unfortunately, the cell link will stay the same reference and not auto-update to the next row like a formula would. For this you would need to write a small VBA macro to loop through all combo boxes and renumber their references, or just manually loop through them in the VBA properties box, e.g. Select "LinkedCell" and start typing the reference for box 1, press tab, press down arrow, press shift tab, type reference for box 2, repeat...