Help with Excel ActiveX listbox controls

[programmer123]

Hi!

I’m trying to create listboxes that displays data from a named range spanning multiple columns. I am able to populate the listbox with data and use it without any problems. However, if the sheet that contains the listboxes changes, the size/dimensions of the listbox unexpectedly also changes. Since I have not written any procedures that control the height/width properties of the listboxes, I cannot determine why the dimensions of the listboxes are changing.

I have attached a spreadsheet that illustrates this problem. When you open the spreadsheet, you should see two listboxes named ListBox1 and ListBox2, which refer to the “A” dynamic named (G1:H6) range and “AA” dynamic named range ((J1:K6) respectively. Now, if you make any type of change anywhere on the worksheet (e.g. enter any value in cell C1), the dimensions of Listbox2 changes but not the dimensions of Listbox1. Why?

By the way, the problem does not seem to be related to named ranges: the error occurs even if you set the ListFillRange to a fixed cell range.

Thanks for your help.

Amit