ActiveX Controls Vs. Form Controls Excel with code example

Controls

Are objects referred as Controls, can be directly placed on the sheet to capture data or capture user inputs. Excel supports rich collection of ActiveX controls which makes Excel very powerful tool. There are many ActiveX controls provided by Microsoft, however, developer can also bring external ActiveX controls by referring external libraries in the Excel. There are two types of controls available in Office application:

ActiveX Controls

1. Code can be written directly behind Sheet object.
2. Events get generated directly behind sheet object.
3. Can execute external macros.
4. Sometime requires application Trust which depends on user’s machine and its security levels.
5. ActiveX controls can be used on worksheet forms or VBA UserForms with or without VBA code. In general, use ActiveX controls when you want to perform design with less code but if you need more flexible design than use Form controls.
6. ActiveX controls have extensive properties that you can use to customize their behavior setting various properties.
7. You can also control different events that occur when an ActiveX control is interacted with.
8. ActiveX controls cannot be added to chart sheets or XLM macro sheets. You also cannot assign a macro to run directly from an ActiveX control the same way you can from a Form control.

Form Controls

1. Supports macros which is scoped to public in VBA.
2. Form controls are the original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.
3. You can use Form controls when you want to easily reference and interact with cell data without using VBA code.

In addition to the standard properties available for ActiveX controls, the following properties can be used with ActiveX controls in Microsoft Excel:

Properties

• BottomRightCell
• LinkedCell
• ListFillRange
• Placement
• PrintObject
• TopLeftCell
• ZOrder

Above properties can be set and returned using the ActiveX control name. The following example scrolls the workbook window so CommandButton1 is in the upper-left corner.

Code example

Set t = Sheet1.CommandButton1.TopLeftCell
With ActiveWindow
    .ScrollRow = t.Row
    .ScrollColumn = t.Column
End With

Some Microsoft Excel Visual Basic methods and properties are disabled when an ActiveX control is activated. For example, the Sort method cannot be used when a control is active, so the following code fails in a button click event procedure (because the control is still active after the user clicks it).

Code example

Private Sub CommandButton1.Click
    Range("a1:a10").Sort Key1:=Range("a1")
End Sub

You can work around this problem by activating some other element on the sheet before you use the property or method that failed. For example, the following code sorts the range:

Code example

Private Sub CommandButton1.Click
    Range("a1").Activate
    Range("a1:a10").Sort Key1:=Range("a1")
    CommandButton1.Activate
End Sub

Remarks

• Controls on a Microsoft Excel workbook embedded in a document in another application will not work if the user double clicks the workbook to edit it. The controls will work if the user right clicks the workbook and selects the Open command from the shortcut menu.
• When a Microsoft Excel workbook is saved using the Microsoft Excel 5.0/95 Workbook file format, ActiveX control information is lost.
• The Me keyword in an event procedure for an ActiveX control on a sheet refers to the sheet, not to the control.

Next >> User Controls in Excel

Share on Facebook

Tweet