In excel, you can make data entry on your worksheets much easier and faster by using forms and various controls and objects that you can add to those forms. A worksheet form is not exactly something like an Excel template; while a template is simply a pre-formatted file, a form contains controls such as boxes and dropdown lists that make it easier to enter and edit data.
However, when creating Excel forms, people often question whether ActiveX controls or form controls are better. Indeed, Excel provides these two types of controls for you to use. But which is the one that you should actually use? See the comparisons between ActiveX controls and form controls below.
Form controls are actually the original controls of Excel. They are built in to Excel and are compatible with Excel’s earlier versions starting from Excel 5.0. They can be used on XLM macro sheets. In general, you use form controls when you want to reference and interact with cell data easily without using any VBA code, and also when you want to put controls into your chart sheets. In addition, you can also run macros by using form controls. Whenever a user clicks the control with a macro, it runs the macro.
However, you can’t use these controls to control events, to run web scripts on web pages, and on UserForms. Also, form controls’ text field, combo list, combo drop-down, and run dialog buttons are unavailable in Excel 2007 workbooks, and are only available in Excel 5.0 dialog sheets.
On the other hand, ActiveX is loaded separately from Excel. You can use ActiveX controls on worksheet forms with or without VBA code and on UserForms. In general, you use ActiveX controls if you need a more flexible design that form controls can’t provide. ActiveX controls are superior in a way that they have extensive properties, allowing you to customize their behavior, appearance, fonts, and other characteristics. You can also use ActiveX controls to control events. For example, you can query a database to refill a list box with items when the user clicks a button. You can also write macros to respond to events.
However, you can’t add ActiveX controls to chart sheets and XLM macro sheets. Also, ActiveX is a Microsoft-based technology; it is not supported on Macs. That’s something to consider if the Excel forms are going to be accessed by Mac users. Then again, many computers will not trust ActiveX by default, thus you may need to manually add it to the trust center on the users’ computers.
|ActiveX Controls||Form Controls|
|- Loaded separately||- Built in to Excel|
|- More flexible and customizable||- Simple, not as flexible/customizable|
|- Can be used on UserForms||- Can be used on chart sheets and XLM macro sheets|
|- Can control events||- Can’t control events|
|- Not supported on Macs||- Accessible on Macs|
In general, you should use form controls if your requirements are simple. Form controls are also the way to go for chart sheets and XLM macro sheets, and if you need it to be accessible on a Mac. However, if you need a more flexible and more customizable design, you should use ActiveX controls.