combo boxes pile up when hiding rows

[K. Georgiadis]

I have a worksheet with two data sets (about 36 rows each), one below the other, each representing a different fundamental strategy of product development. Each data set has 4 combo boxes that control scenarios in four different business variables.

For a variety of reasons, the least complicated way to control which data set is used for further calculation is to hide the data set that does not apply through a toggle mechanism. This works fine except that all four combo boxes from the hidden range pile up on top of one another, like Lego blocks, and act as a distraction.

Is there any way to make the combo boxes also disappear temporarily or is this the nature of the beast?

[Bob Phillips]

You could just change the Visible proiperty of the combobox when the associated rows get hidden and vice versa.

[K. Georgiadis]

How is that done? I am not even sure how these combo boxes are addressed in the VBE. In Excel's Name Box they are defined as Drop Down 1, 2...all the way to 109, but I doubt that syntax would work in VBE. I should clarify that these combo boxes were created from the Forms toolbar and not from the Control Toolbox.

[Bob Phillips]

Then it is just

[vba]

Activesheet.Dropdowns("Drop Down 1").Visible = False
[/vba]


and so on

[K. Georgiadis]

Can I replace "Activesheet" with "Worksheets("Sheet name")?

[Bob Phillips]

Yep.

[mikerickson]

In addition to hiding the comboboxes, you might also want to set the properties of the combo boxes to "Don't move or size with cells"