Register now or log in to join your professional community.
There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls. The Forms controls aren’t very complicated, and they have been part of Excel for longer than the Controls Toolbox (Excel97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behavior to the ActiveX controls from the Controls Toolbox. I’ve recently had problems with a buggy slider from the Forms toolbar, but I suspect the parent workbook was corrupt.
There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls. The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms. In Classic Excel and VBA, Forms and ActiveX controls are available on toolbars, while in Excel2007, they’ve been moved to the Developer tab of the Ribbon.
Dear Sir,
Great Thanks to invite me for answering your question, and I hope my answer will be helpful to your inqury:
There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls
Form controls are built in to Excel whereas ActiveX controls are loaded separately. You can add extra ActiveX controls, not Form controls.
Form Control:
n The Forms controls aren’t very complicated, and they have been part of Excel for longer (I’m thinking Excel4, and they were used in Excel5/95′s dialog sheets) than the Controls Toolbox (Excel97), so it stands to reason that they’d be more seamlessly integrated.
n Forms controls can be used on worksheets and chart sheets.
n Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel2007).
n Forms controls can be formatted as if they were regular shapes (Excel95 to Excel2003 shapes; the new shapes in Excel2007 are different animals altogether), although the options are not as extensive as for shapes, and vary for different control types.
i) The button dialog is much like that for shapes with various Font, Size, Alignment, and similar tabs, although the button is stuck with its gray colour and rectangular shape.
ii) A regular shape can be assigned a macro in much the same way, with all the shape’s formatting options.
iii) Also depending on the control, there is a tab for properties specific to the control. For a Drop Down or List Box control, there is a means to select an input range, containing the list of items appearing in the control, and another for a cell link, which is where the selected item in the control is stored.
n The code is always written in a general module. Right Click the control and using assign macro option choose the right procedure that you want to call on the click/change in that control. To see code for form controls go to Form Control module
Active X Control:
n Being newer, the ActiveX controls have richer formatting possibilities.
n Generally you'll use Forms controls, they're simpler. ActiveX controls allow for more flexible design and should be used when the job just can't be done with a basic Forms control.
n Many users’ computers by default won't trust ActiveX, and it will be disabled; this sometimes needs to be manually added to the trust centre.
n ActiveX is a microsoft-based technology and, as far as I'm aware, is not supported on the Mac. This is something you'll have to also consider, should you (or anyone you provide a workbook to) decide to use it on a Mac.
n ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel. (Neither does VBA at all in fact, if you’re using MacOffice2008.)
n ActiveX controls have a scaled back Format Control dialog, but a rich formatting environment offered by the Properties window. In addition, some controls have properties which indicate the addresses for List Fill Range and Linked Cell.
n To see code for form control, go to sheet module. While in the design mode (first button in toolbox) double-click the control to open its code window.
Applications:
a- Excel responds to a Forms control after the user finishes interacting with it (i.e., unclicks).
b- Excel responds continuously to an ActiveX control.
c- If a Forms slider control is linked to a cell’s value, the user has to slide the bar on the control, and then release it before the cell updates.
d- If an ActiveX ScrollBar is linked to the cell’s value, the cell updates continuously as the user slides the bar along the control.
e- Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behaviour to the ActiveX controls from the Controls Toolbox.
f- There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls.
1- The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms.
2- In Classic Excel and VBA, Forms and ActiveX controls are available on toolbars, while in Excel2007, they’ve been moved to the Developer tab of the Ribbon.
Comparison:
- ActiveX controls are visually more appealing and have more formatting options available than form controls.
- ActiveX controls trigger events while Form controls call macros assigned to them.
- One strong reason due to which I would suggest using form controls is that sometimes events for ActiveX controls are triggered on its own when some other event is triggered.
- At times, on opening the workbook ActiveX controls are not recognised and give errors.
Using Controls with VBA Procedures
- Forms controls can have macros assigned to them. Clicking on the control runs the macro. The Assign Macro dialog appears when a control is first created, or when the corresponding item is selected from the right click menu. Any macros in the active workbook or in any open workbooks appear in this list. The default macro is named like an event procedure (“Click”), but that seems appropriate to the ActiveX Controls.
- The ActiveX controls have event procedures that run when they are clicked. These procedures are either inserted in the code modules of the objects (i.e., worksheets) within which they are embedded, or written in class modules. Right click on an ActiveX control and select View Code: The shell of the default procedure is inserted into the code module of the object the control is embedded within (i.e., the worksheet). Enter the code here that should be run when the control is clicked.
- Note the two dropdowns at the top of the code module. The left dropdown lists all objects associated with the object, including the object itself (Worksheet) and its embedded controls.
- When an object is selected in the left dropdown, the right dropdown shows the events available to that control. This provides the ActiveX controls with much greater functionality than the Forms controls.
= In general I use the Forms controls, unless I need the continuous response of an ActiveX scrollbar.99% of the time I use controls from the controls toolbox as I like the richset of properties and methods and the easy of referencing the controls incode. That is not to say the forms controls don't have a place though.
Regards,
Lubna Al-Sharif
Generally you will use Forms controls, they're simpler. Form controls are backed into Excel itself
ActiveX controls allow for more flexible design and should be used when the job just can't be done with a basic Forms control. Many user's computers by default won't trust ActiveX, and it will be disabled; this sometimes needs to be manually added to the trust center. ActiveX is a Microsoft-based technology and, as far as I'm aware, is not supported on the Mac.
Generally you'll use Forms controls, they're simpler. ActiveX controls allow for more flexible design and should be used when the job just can't be done with a basic Forms control.
Many user's computers by default won't trust ActiveX, and it will be disabled; this sometimes needs to be manually added to the trust center. ActiveX is a microsoft-based technology and, as far as I'm aware, is not supported on the Mac. This is something you'll have to also consider, should you (or anyone you provide a workbook to) decide to use it on a Mac.
You are right, Anil Sharma.
Although they look similar, they’re quite different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are typically used on Excel Userforms. As a general rule, you always want to use Form controls when working on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring Form controls is far easier than configuring their ActiveX counterparts.
To perform ActiveX controls, you have to be awared of VBA (Visual Basic for Applications) to perform it.
But for me, I find ActiveX controls are far more fun. I use it seasonally as cosmetics to design amazing reports.
Form controls are built in to Excel whereas ActiveX controls are loaded separately.
Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.