Difference between a Forms Control verus Active-X Control

[funGT350]

What is the difference Between a Forms Control versus an Active-X Control?
(e.g. a Combo Box from the Forms Toolbar versus a Combo Box from the Control
Toolbox Toolbar?) Why would one use one versus the other?

 

[Jim Thomlinson]

The controls from the forms toolbar are part of XL. You attach macros from
modules directly to the control. This can be handy as the controls are not
embeded in the sheet and they do not place any code in the sheet.

Controls from the control toolbox are embeded objects. If you are fimiliar
with VB6 these controls are almost identical to the controls you would embed
in a userform. Since these controls are embedded they place their code
directly in the sheet. The up side is that they have a rich set of properties
and methods. Much more than controls from the forms toolbar.

99% of the time I use controls from the controls toolbox as I like the rich
set of properties and methods and the easy of referencing the controls in
code. That is not to say the forms controls don't have a place though.

 

[Ken Johnson]

What is the difference Between a Forms Control versus an Active-X Control?
(e.g. a Combo Box from the Forms Toolbar versus a Combo Box from the Control
Toolbox Toolbar?) Why would one use one versus the other?

Also, if you want your spreadsheet to work on a Mac use controls from
the Forms toolbar.

Ken Johnson

 

[Jon Peltier]

The Forms controls have been part of Excel for longer (I'm thinking Excel 4)
than the Controls Toolbox (Excel 97), 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.

The Forms controls can be have macros assigned to them. 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) they
are embedded in, or written in class modules.

Forms controls can be used on chart sheets, ActiveX controls cannot. ActiveX
controls do not work in MacExcel.

Excel responds to a Forms control after the user finishes interacting with
it (i.e., unclicks). Excel responds continuously to an ActiveX control. For
example, suppose a scrollbar is linked to a cell's value. If the control is
a Forms slider, the user has to slide the bar on the control, then release
it before the cell updates. If the control is an ActiveX Scrollbar, the cell
updates continuously as the user slides the bar along the control.

In general I use the Forms controls, unless I need the continuous response
of an ActiveX scrollbar.

- Jon

 

[Ken Johnson]

The Forms controls have been part of Excel for longer (I'm thinking Excel 4)
than the Controls Toolbox (Excel 97), 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.

The Forms controls can be have macros assigned to them. 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) they
are embedded in, or written in class modules.

Forms controls can be used on chart sheets, ActiveX controls cannot. ActiveX
controls do not work in MacExcel.

Excel responds to a Forms control after the user finishes interacting with
it (i.e., unclicks). Excel responds continuously to an ActiveX control. For
example, suppose a scrollbar is linked to a cell's value. If the control is
a Forms slider, the user has to slide the bar on the control, then release
it before the cell updates. If the control is an ActiveX Scrollbar, the cell
updates continuously as the user slides the bar along the control.

In general I use the Forms controls, unless I need the continuous response
of an ActiveX scrollbar.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

Hi John,

Any idea about the usefulness of the Multi and/or Extended Selection
properties of the Forms List Box. I can't figure out how to make them
work.

Ken Johnson

 

[Jon Peltier]

Ken -

If you have a single select listbox or combo, the cell link contains the
list index, that it, the index of the selected item. If multiple selected
items are enabled (extended or multiple selections), then list index is
undefined. But you can get the information out using VBA:

Sub Macro1()
Dim lst As ListBox
Dim b() As Boolean
Dim i As Long

Set lst = ActiveSheet.Shapes("List Box 1").OLEFormat.Object

ReDim b(1 To lst.ListCount)

For i = LBound(b) To UBound(b)
b(i) = lst.Selected(i)
Next

ActiveSheet.Range(lst.LinkedCell).Resize(lst.ListCount).Value _
= WorksheetFunction.Transpose(b)
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Ken Johnson]

Ken -

If you have a single select listbox or combo, the cell link contains the
list index, that it, the index of the selected item. If multiple selected
items are enabled (extended or multiple selections), then list index is
undefined. But you can get the information out using VBA:

Sub Macro1()
Dim lst As ListBox
Dim b() As Boolean
Dim i As Long

Set lst = ActiveSheet.Shapes("List Box 1").OLEFormat.Object

ReDim b(1 To lst.ListCount)

For i = LBound(b) To UBound(b)
b(i) = lst.Selected(i)
Next

ActiveSheet.Range(lst.LinkedCell).Resize(lst.ListCount).Value _
= WorksheetFunction.Transpose(b)
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

Fantastic Jon!
Thank you very much.

Ken Johnson