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!

 

[vifferfun]

One weird thing that I just noticed -- in the ControlBox properties I see the incorrect names (i.e. Combo1, Combo2, etc.), but in the "Name Box" in the top-left of the screen, it shows the correct name (i.e. cbDept18, cbDept19, etc.). The two don't reconcile, which doesn't make any sense at all.

I hope someone can help me. This Excel bug is driving me insane and essentially making my workbook useless (and it took many hours to create it)

 

[vifferfun]

Also, if I try to rename "Combo1" to "cbDept18" in the properties window, I get an Excel error "Ambiguous name detected: cbDept18". I'm at a loss

 

[asadmasad]

I have the exact same problems as mentioned by vifferdun. Will appreciate a response to these. Does this has something to do with the Dec 2014 Office update which had caused some issues with the ActiveX controls?

Thanks heaps in advance.

 

[ZeroPauper]

"Cannot insert object" error in an ActiveX custom Office solution after you install the MS14-082 security update

Scroll down to loop for Symptom 3 and follow the instructions, I'm not sure if it will fix this problem but it's worth a try.

 

[asadmasad]

Yes perfect ZeroPauper. It's all good now..thanks heaps!!!

 

[ZeroPauper]

Yes perfect ZeroPauper. It's all good now..thanks heaps!!!

You're welcome!

 

[vifferfun]

Hi ZeroPauper, and thanks for your response

I figured that this issue was probably related to the MSForms.exd issue. Do you know how to tell whether or not a computer has the "MS14-082" update installed? I have been working on the Excel Workbook in three different locations:

• Computer PL -- Personal Laptop
• Computer PD -- Personal Desktop
• Computer WL -- Work Laptop

I noticed that the ComboBoxes were correctly named (and the workbook was functioning correctly) on Computers PL and PD. I saved the file on PD and then tried to open it on WD but the ComboBoxes were incorrectly renamed and the workbook wouldn't function. The IT Dept for my employer (a large company) who maintains WD likely doesn't allow Microsoft to install updates automatically, so I wonder if they held off on this update due to the issues?

On a related note, could this same issue be causing my ActiveX ComboBoxes to incorrectly resize themselves each time I print and then save the file?

Thanks again!

 

[RoryA]

On a related note, could this same issue be causing my ActiveX ComboBoxes to incorrectly resize themselves each time I print and then save the file?

No - that's a separate and very longstanding bug.

 

[vifferfun]

No - that's a separate and very longstanding bug.

Thanks RoryA! I've kind of learned to live with it by not saving the Excel file after I've printed it (although it's frustrating that it doesn't work). The good news is that it only resizes the ComboBoxes on the tabs that I print, and I have to delete the printed tabs anyway Hopefully MS comes up with a solution though.