Combo Box Loading
 

I have a combo box on a worksheet that requires loading with worksheets
names if a certain criteria is met by the worksheet.

I have the names of the worksheets required listed on the worksheet via
formula.

Initially I created a dynamic named range which covered just the rows I
required and set the ListfillRange with this dynamically named range. The
problem with this was that every time anything was changes on any of the
workbooks the Change event of the combo box was fired!

I then changed the dynamically named range to a static range and tried to
use the DropButtonClick event of the combo box to created the combo options
using AddItem. If managed to only add the sheet names I required and
ignored the others. The first problem with this was that when you use the
down arrow the list gets long as worksheets names are added multiple time.
I got around this by clearing before each routine. This them presented me
with the problem that the users selection was not displayed.

The methods I have used are obviously not functional, so does anyone have
any idea how I can populate a combo box with selective options. The options
need to be checked everytime the user clicks the dropdown arrow.