ActiveX ComboBox Issues -- Excel 2010 is Automatically Renaming my Controls

[vifferfun]

Hi Everyone!

I have a workbook consisting of about 40 tabs, and each tab has about 40 ActiveX ComboBoxes. Half of the comboxboxes are populated by linking to a range, and the other half is populated using VBA (since they are conditional on the selections in the first half of comboboxes). Unfortunately, I have been having a lot of issues that are driving me nuts

MAIN ISSUE

Every once in a while, I notice that Excel is renaming all of my comboxes, and this causes my VBA code to fail (since it refers to the ComboBoxes by their name). The names change from cbDept18, cbDept19, etc. to Combo1, Combo2, etc. When this happens, I have to revert to an older version of my file and recreate all of the changes that I made since the prior version. Redoing work is never fun Is there something that I'm doing wrong, or does anyone know if there is a known bug that causes this issue?

BACKGROUND INFO:

• I work on the same file on my work computer and my home computer, both running Excel 2010
• I'm not sure when the ComboBoxes are renaming . . . I don't notice the error until I try to click a VBA-populated ComboBox and find that it is empty
• the file is saved as XLSB
• Other problems that I have encountered include:
  • ComboBoxes sometimes become completely disabled unless I delete the "MSForms.exd" temporary file (which is apparently a fix for a known MS bug)
  • ComboBoxes completely resize and reposition themselves, which I think happens after I print and then save
    • Based on another MrExcel thread, I have changed the "Placement" attribute to 1 (i.e. Move and size with cells) for all comboboxes, so I'll see if that helps
  • The XLSB file takes a long time to open
    • It usually says "Not responding" as it calculates for a minute, then pops to life
    • Maybe due to the (40 tabs) x (40 ComboBoxes) = 1600 ActiveX ComboBoxes in the workbook?

Thanks in advance for any help!

 

[RoryA]

Hopefully MS comes up with a solution though.

Not going to happen.

 

[vifferfun]

Not going to happen.

Kind of strange that MS will allow a product to be broken indefinitely :S Do people just avoid ActiveX controls altogether? I think I went the ActiveX route so that I could control the number of items appearing in the dropdown (since I like to view about 40 items at a time). I don't think I could do this with the regular ComboBox. I think the regular ComboBox also has issues related to small/unreadable text size within the drop-down list or something like that.

I have already invested a lot of time into this workbook based on ActiveX controls, I'm not sure if I should invest more time trying to fix it, or just live with the ActiveX flaws

 

[RoryA]

Personally I avoid ActiveX on worksheets if at all possible. If I do need the additional functionality, I'd prefer to put them on a userform.

 

[vifferfun]

Thanks Rory. I don't think I could have avoided ActiveX because I need to populate the ComboBoxes on the fly, allow the user to type an entry into the box (and it finds the closest match), etc. It's too bad that MS won't fix errors in their software. I guess I will just have to live with the flaws. The good news is that I'm the sole user of this specific workbook, so I can manage the workarounds.

Thanks!