Controls: ActiveX vs. Forms
There are two kinds of controls that you can put on a worksheet, ActiveX controls from the Control Toolbox and Forms controls from the Forms (or Drawing) toolbar. Kevin, like others before him, asks how to decide which ones to use. I don’t know, but I’ll tell you how I decide.
In general, the two types of controls are similar in that they work the same way. That is, a textbox from the Drawing toolbar is a control in which you can type text. That’s fundamentally the same as the ActiveX textbox. Checkboxes of either type are used to select or deselect an option.
Obviously, a decision to use one or the other will based on their differences, not their similarities. The primary differences, for me anyway, are events and formatting. ActiveX controls give the spreadsheet designer a lot more flexibility when it comes to displaying or reacting to events of controls. Viewing the properties of an ActiveX control demonstrates how much more you can do with them than with Forms controls. Also, Forrms controls have one basic event, the click event, which you access by assigning a macro to the control. ActiveX controls, on the other hand, have a lot of events. The ActiveX combobox, for instance, has fifteen events.
All that flexibility comes at a cost (you knew it would). ActiveX controls carry a lot more overhead with them and have a history of being buggy.
Weighing the good and the bad, I always use Forms controls unless I have a specific reason to use an ActiveX. Forms controls are lean and mean and simple to use. If, however, I need a combobox to display a certain font, I have no choice but to use an ActiveX control. Similarly, if I need to avoid certain characters in a textbox, I’ll need the KeyPress event which is not available from the Forms textbox. So my advice, and my practice, is to use Forms controls unless I need functionality that only ActiveX controls offer.
Rich:
As one who works on both the Windows and Mac side, I always use Forms, because MS does not include ActiveX controls on the Mac side. Therefore, cross-platform work mandates Forms controls.
11 August 2004, 10:50 amRichard:
I concur with Rich.
11 August 2004, 5:25 pmDick:
Rich, Richard and Dick, all in the same comments section. That’s a great point, Rich. I haven’t used a Mac in about 8 years. Thanks for the contribution.
12 August 2004, 7:53 amJamie Collins:
I use the ActiveX controls because I am invariably using classes i.e. I can use a WithEvents object variable that references an ActiveX control.
The Forms controls can only run a single public Sub located in a standard module, I guess because it is using a callback that requires the procedure’s memory address. Standard modules don’t really fit with my OOP development approach.
So if my control requires code then I will use the ActiveX version. Yes, they are buggy, some more than others. It pays to do testing and googling to be aware of any issues before investing too much development time.
Jamie (Sorry, not a Richard).
–
13 August 2004, 2:51 amross:
The main version of excel where I work is 97, so I am forced to use form controls with chart sheets (I think this might have changed in later versions, I’m not sure now!). But apart form that I use activeX as I much prefer the “control” I can have over the control. I’ve never really come across any bugs - save the odd button taking a while to show up - and on the scale of development I’m doing, and with modern PC’s, over heads have never really been an issue. Interesting points though because I would never have thought about the advantages of using form controls.
13 August 2004, 3:32 amI have often wondered, (well, maybe not often, but once or twice defiantly) if form control will be removed - esp. if .net is introduced
Andy Miller:
I do believe that the Form controls are there as left-overs from past versions. I tend to not use them often for a couple reasons. For one, like has been mentioned before, I like the control over the variable that the ActiveX controls give me. For another, I once had a workbook that had quite a large number of form controls (it was one that I had inherited, and it was created in an older version of Excel) get corrupted on me. Most of my calls to public functions got corrupted, including any sub called by a form control. Also, I do have a fear of relying on the ‘old’ controls knowing that Microsoft may decide not to support them with any new version.
13 August 2004, 7:20 amDoug:
My preference is to use ActiveX controls. I find them more secure & easier to protect (location, size, macro) from tampering or accidental changes & corruption.
13 August 2004, 9:34 amthomas david:
I have been using the Textbox Active-X Control, but could really use something where the the font size and attributes for text inside the box can be changed. Perhaps there is a way with the standard Excel control but it is not readily apparent to me, or maybe another Textbox tool that does this is available?
20 April 2006, 3:17 pm