Yesterday I was working with an Excel document that contained Combobox form controls.

I wanted to count the number of cells containing a particular value using the COUNTIF formula. However the count returned 0 because the LinkedCell property of the Combobox was not set to the Cell that contained the Combobox.

To set the LinkedCell Ctrl-Click the Combobox to select it, right-click and select Format Control. Then set the correct Cell in the Cell link field:


My sheet contained about 150 Comboboxes, so obviously I was going to do this using a script. I couldn’t find anything useful with Google so I wrote my own Macro.

The Macro assumes that the sheet containing the Comboboxes is the active sheet and only Comboboxes are touched. It also assumes that Comboboxes are places within Cells an do not overlap multiple Cells.

While the script runs, progress is indicated in the Application StatusBar in the bottom left corner: