Not long ago, Microsoft had an update to Excel that broke a lot of ActiveX Control Boxes. If you are having this problem, Microsoft has a fix and a support article.
Excel Cannot Insert Object ActiveX – Microsoft Support Document: http://support.microsoft.com/en-us/kb/3025036
Several solutions are offered in the article. Te main solution is to ensure all parameter types are VBA friendly. Delete TEMP *.exd file if necessary.
Pros and Cons of ActiveX
- Pros:
- More robust than form controls
- More flexible
- Multiple Event Handlers
- Cons:
- Font can get bigger and move
- Workbooks can get corrupted
Kevin mentions that he avoids them like the plague. Jordan mentions that they are a leftover from Visual Basic 6.0 and that they don’t play nicely with Excel
ActiveX Best Practices
Kevin Jones tends not to use Controls on a spreadsheet other than Command Buttons.
- Command Buttons should be Form Controls
- Drop Down Menus should be done via validation
- Combo Boxes, Radio Buttons, Check Boxes are best placed on User Forms.
This is more aesthetically pleasing and they are much easier to manage and maintain.
- The Comprehensive Guide to the Excel Ribbon: Making the Most of Your Data - January 31, 2023
- 51: Oz du Soleil & the Global Excel Summit 2021 - February 8, 2021
- 50: Randy Austin – Excel for Freelancers - January 22, 2021
