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.
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.
I concur with Rich.
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.
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).
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.
I have often wondered, (well, maybe not often, but once or twice defiantly) if form control will be removed – esp. if .net is introduced
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.
My preference is to use ActiveX controls. I find them more secure & easier to protect (location, size, macro) from tampering or accidental changes & corruption.
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?
I am having some trouble with my embedded Active X forms.
I have a number of embedded controls on a worksheet (text boxes, combo boxes and labels) inserted using the Control Toolbox.
When the controls become activated, the height, width and text grow.
When they loose focus they return to the original size and text size.
An image of the effect is here: http://img511.imageshack.us/my.php?image=image1he9.gif
I am running Excel 2003 and have Microsoft Forms 2.0 Object Library referenced. However, the same thing happens with Excel 2007.
The following kind of fixes the problem
Private Sub TextBox1_LostFocus()
TextBox1.Width = 99.75
TextBox1.Height = 14.25
TextBox1.Font.Size = 10
End Sub
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox1.Width = 99
TextBox1.Height = 13.5
TextBox1.Font.Size = 9
End Sub
However it only works when you select the form and then click in the workbook. If you select one Active X controls and then select another, the previously selected control forms grows until you wiggle the mouse and then it shrinks again.
The form controls (inserted using the forms toolbox) do not have this problem.
Any help would be greatly appreciated.
Thanks in advance.
Calan.
How to get change event for form controls in Excel.
Forms controls don’t have change events, only an OnAction property. You have to use ActiveX controls if you want events.
But i want to use Change event property in Excel 2007. And ActiveX controls does’t works in 2007.