Excel的表单控件和控件之间的差异ActiveX控件 [英] Differences between Excel's Form Controls & ActiveX Controls
问题描述
为什么Excel中有 2种控件类型?(2个按钮,2个组合框,2个复选框等...)
Forms控件和 ActiveX控件有什么区别?我应该使用哪一个?
我发现某些代码示例可与控件在线交互,而其他示例则不行.怎么来的?
如何处理每种类型,如何区分?
Excel可用的两种控件类型存在[永恒]的困惑-相对的术语加剧了这种情况由不同的在线资源使用.
这只是表单控件和 ActiveX控件 之间差异的一般概述(基于我的旧笔记,这些笔记最终帮助我弄清楚了找出差异!)请访问随附的链接,以获取有关每个控件的更多详细信息,包括代码和示例项目.☺
(改编自我的原始帖子.)
例如,让我们比较两种类型的组合框.在某些编程语言中,可比较的控件称为下拉菜单" 或下拉列表" .在Excel中,我们有一个" Form Control Combo Box " 和一个" ActiveX Control Combo Box " :
(点击图片放大.)
☆默认名称"适用于手动创建的控件.以编程方式创建的控件没有(或不需要)默认名称,因此应在创建后立即分配一个默认名称.
(来源:
其他因素可能会影响控件的可用性.
-
Office.com:和 (点击图片放大.)
要重命名,编辑或删除现有的宏,请按 Alt + F8 打开 Macro
界面:
ActiveX控件事件
ActiveX控件具有更广泛的事件列表,可以将其设置为响应事件.
要将事件分配给ActiveX控件,请右键单击该控件,然后选择 View Code
.在VBE中,您可以粘贴代码,也可以从VBE窗口右上方的下拉列表中选择特定事件.
(点击图片放大.)
在用户表单上控制事件的处理:
事件也可以在用户窗体的控件中使用.由于只能将ActiveX控件放置在用户窗体中,因此需要进行更多编码+更多功能"的权衡.
ActiveX控件添加到用户窗体的方式与添加到工作表的方式相同.请记住,分配给用户窗体本身(即背景)的任何事件都会在控件所覆盖的任何区域中被阻止",因此您可能需要将相同的事件分配给控件以及用户表单.
例如,为了使该用户窗体响应 MouseMove
窗体上的任何地方 ,事件代码已应用于用户窗体,文本框,选项按钮和框架:
VBA示例
使用VBA添加/修改/删除表单控件选项按钮:
Sub formControl_add()'创建表单控件昏暗的ws作为工作表:设置ws = ActiveSheet使用ws.Shapes.AddFormControl(xlOptionButton,25,25,100,100).Name ="cOptionButton1"'名称控制立即生效(因此我们以后可以找到它)结束于结束子子formControl_modify()'修改表单控件的属性昏暗的ws作为工作表:设置ws = ActiveSheetws.Shapes("cOptionButton1").Select对于选择",必须在更改之前选择形状.Characters.Text ="wxyzabcd"结束于结束子子formControl_delete()'删除表单控件昏暗的ws作为工作表:设置ws = ActiveSheetws.Shapes("cOptionButton1").删除结束子
使用VBA添加/修改/删除ActiveX命令按钮:
Sub activexControl_add()'创建ActiveX控件昏暗的ws作为工作表:设置ws = ActiveSheet使用ws.OLEObjects.Add("Forms.CommandButton.1")左= 25.顶部= 25宽度= 75高度= 75.Name ="xCommandButton1"'名称控制立即生效(因此我们以后可以找到它)结束于结束子子activexControl_modify()'修改activeX控件的属性昏暗的ws作为工作表:设置ws = ActiveSheet使用ws.OLEObjects("xCommandButton1").Object.Caption ="abcxyz".BackColor = vbGreen结束于结束子子activexControl_delete()'删除activeX控件昏暗的ws作为工作表:设置ws = ActiveSheetws.OLEObjects("xCommandButton1").删除结束子
从表单控件组合框中添加/删除项目:
Sub ComboBox_addRemoveItems_FormControl()昏暗的ws作为工作表:设置ws = ActiveSheet'将项目添加到表单控件组合框ActiveWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem"abcd"'从表单控件组合中删除所有项目ActiveWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems结束子
从ActiveX组合框中添加/删除项目:
Sub ComboBox_addRemoveItems_ActiveXControl()昏暗的ws作为工作表:设置ws = ActiveSheet'将项目添加到ActiveX组合框ActiveWorkbook.Sheets("Sheet1").ComboBox1.AddItem"abcd"'从ActiveX组合框中删除所有项目ActiveWorkbook.Sheets("Sheet1").ComboBox1.Clear结束子
更多信息:
-
Office.com:添加复选框,选项按钮或切换按钮( ActiveX控件)
-
Office.com: 通过选择控件启用选择(选中并列出框)
-
Office.com: VBA形状成员
-
MSDN: 使用表格上的ActiveX控件(Office)
-
Exceldemy: 使用方法Excel中的表单控件
-
Microsoft TechNet: 在Excel 2010中,窗体控件"和"ActiveX控件"有什么区别?
Why are there 2 types of controls available in Excel? (2 buttons, 2 combo boxes, 2 check box, etc...)
What's the difference between Forms Controls and ActiveX Controls? Which one should I use?
Some code samples I find online work with my controls, but others do not. How come?
How do I work with each type, and how can I tell the difference?
There is [eternal] confusion surrounding the two types of controls available to Excel — exacerbated by the contrasting terminology used by different online sources.
This is only a general overview of the differences between Form Controls and ActiveX Controls (based on my old notes that helped me finally figure out the differences!) Visit the included links for more detailed information about each control including code and sample projects. ☺
(Adapted from my original post here — now closed)
Overview:
There are two types of controls: Form controls and ActiveX controls:
Both types of controls can be used on worksheets but only ActiveX controls can be used on userforms.
Form controls are part of the Shapes collection (just like Drawing Objects), and thus are referred to like:
ActiveX controls are basically part of the worksheet and are therefore referred to like:
Both types of controls can be created, modified and deleted from either the worksheet, or programmatically with VBA, however, the 2 types of controls have slightly varying syntax when using VBA to refer to them.
Some sites discuss also discuss a Data Form. This is nothing more than a userform made specifically for data entry/manipulation of data, so it would've made more sense to call them (the more familiar sounding) "Data Entry Userform".
Office documentation also occasionally refers to a worksheet as a form. While this is technically correct, don't let this confuse you. Think of the word "form" as being used in a general sense:
Two Types of Controls
Form Controls
ActiveX Controls
The two look, behave, and are controlled similarly, but not identically. (List here.)
For example, let's compare the two types of Combo Boxes. In some programming languages, comparable controls are referred to as a "drop-down menu" or "drop-down list". In Excel, we have a "Form Control Combo Box", and an "ActiveX Control Combo Box":
(Click image to enlarge.)
☆ "Default name" applies to controls created manually. Controls created programmatically do not have (or require) a default name and therefore should have one assigned immediately upon creation.
(Source: my answer)
Control Availability
This image below shows which common controls are generally available for use in Worksheets and User Forms.
Other factors may affect control availability.
Office.com : Why are form-related commands or controls on the ribbon disabled?
Office.com : Overview of forms, Form controls, and ActiveX controls on a worksheet
MSDN : Add Controls to a User Form
About ActiveX controls and related Security Concerns
An ActiveX control is an extension to the VBA Toolbox. You use ActiveX controls just as you would any of the standard built-in controls, such as the CheckBox control. When you add an ActiveX control to an application, it becomes part of the development and run-time environment and provides new functionality for your application.
An ActiveX control is implemented as an in-process server (typically a small object) that can be used in any OLE container. Note that the full functionality of an ActiveX control is available only when used within an OLE container designed to be aware of ActiveX controls.
This container type, called a control container or control object, can operate an ActiveX control by using the control’s properties and methods, and receives notifications from the ActiveX control in the form of events. The following figure demonstrates this interaction:
See also:
Wikipedia: ActiveX
Symantec.com : Discussion of ActiveX Vulnerabilities
How-To Geek : What ActiveX Controls Are and Why They’re Dangerous
Option Buttons (Radio Buttons)
In Excel, the two types of radio buttons are actually called Option Buttons. To further confuse matters:
the default name for the form control is
OptionButton1
.the default name for the ActiveX control is
Option Button 1
.
A good way to distinguish them is by opening the control's Properties list (on the ribbon under the Development tab, or by right-clicking the control and choosing Properties
, or hitting F4), because the ActiveX control has many more options that the simpler form control.
Option buttons and checkboxes can be bound together (so only one option at a time can be selected from the group) by placing them in a shared Group Box.
Select the group box control and then hold Ctrl while selecting each of the other controls that you want to group. Right-click the group box control and choose
Grouping
→Group
.
The first two links below are separate sets of instructions for handling each type of option button.
HANDLING CONTROL EVENTS:
Form control events (Click
event only)
Form control events are only able to respond to one event: the Click
event. (More info here.) Note that this section doesn't apply to userforms since they use only ActiveX controls.
To add a procedure for the Click
event:
Right-click the control and choose
Assign Macro...
In the 'Assign Macro` Dialog:
Select an existing procedure, and click OK, or,
Create a new procedure in the VBE by clicking New..., or,
Record a new macro by clicking Record..., or,
to Remove the assigned event, delete its name from
Macro Name
field and click OK.
(Click image to enlarge.)
To rename, edit or delete existing macros, hit Alt+F8 to open the Macro
interface:
ActiveX control events
ActiveX controls have a more extensive list of events to which they can be set up to respond.
To assign events to ActiveX controls, right-click the control and choose View Code
. In the VBE, you can paste in code, or choose specific events from the drop-down list at the top-right of the VBE window.
(Click image to enlarge.)
Control event handling on a userform:
Events can also be used in controls on userforms. Since only ActiveX controls can be placed a userform, you'll have the "more coding + more functionality" trade-off.
ActiveX controls are added to userforms the same way as they are added to a worksheet. Keep in mind that any events assigned to the userform itself (ie., background) will be "blocked" in any areas covered up by a control, so you may need to assign the same events to the controls as well as the userform.
For example, in order to make this userform respond to MouseMove
anywhere on the form, the same event code was applied to the userform, textboxes, option buttons and the frame:
VBA EXAMPLES
Add/Modify/Delete a form control option button using VBA:
Sub formControl_add()
'create form control
Dim ws As Worksheet: Set ws = ActiveSheet
With ws.Shapes.AddFormControl(xlOptionButton, 25, 25, 100, 100)
.Name = "cOptionButton1" 'name control immediately (so we can find it later)
End With
End Sub
Sub formControl_modify()
'modify form control's properties
Dim ws As Worksheet: Set ws = ActiveSheet
ws.Shapes("cOptionButton1").Select
With Selection 'shapes must be Selected before changing
.Characters.Text = "wxyzabcd"
End With
End Sub
Sub formControl_delete()
'delete form control
Dim ws As Worksheet: Set ws = ActiveSheet
ws.Shapes("cOptionButton1").Delete
End Sub
Add/Modify/Delete an ActiveX command button using VBA:
Sub activexControl_add()
'create ActiveX control
Dim ws As Worksheet: Set ws = ActiveSheet
With ws.OLEObjects.Add("Forms.CommandButton.1")
.Left = 25
.Top = 25
.Width = 75
.Height = 75
.Name = "xCommandButton1" 'name control immediately (so we can find it later)
End With
End Sub
Sub activexControl_modify()
' modify activeX control's properties
Dim ws As Worksheet: Set ws = ActiveSheet
With ws.OLEObjects("xCommandButton1").Object
.Caption = "abcxyz"
.BackColor = vbGreen
End With
End Sub
Sub activexControl_delete()
' delete activeX control
Dim ws As Worksheet: Set ws = ActiveSheet
ws.OLEObjects("xCommandButton1").Delete
End Sub
Add/Remove items from a form control combo box:
Sub ComboBox_addRemoveItems_FormControl()
Dim ws As Worksheet: Set ws = ActiveSheet
'add item to form control combo box
ActiveWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem "abcd"
'remove all items from from form control combo bo
ActiveWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems
End Sub
Add/Remove items from an ActiveX combo box:
Sub ComboBox_addRemoveItems_ActiveXControl()
Dim ws As Worksheet: Set ws = ActiveSheet
'add items to ActiveX combo box
ActiveWorkbook.Sheets("Sheet1").ComboBox1.AddItem "abcd"
'remove all items from ActiveX combo box
ActiveWorkbook.Sheets("Sheet1").ComboBox1.Clear
End Sub
More Information:
Office.com : Add a checkbox or option button (Form controls)
Office.com : Add a checkbox, option button, or toggle button (ActiveX controls)
Office.com : Overview of forms, Form controls, and ActiveX controls on a worksheet
Office.com : Enable selection through choice controls (check and list boxes)
Office.com : Add, edit, find, and delete rows by using a data form
MSDN : VBA Shape Members
Exceldemy : How to Use Form Controls in Excel
MSDN : Using Windows Forms Controls on Excel Worksheets (Visual Studio)
Microsoft TechNet : Behaviour of ActiveX controls embedded in Office documents
Stack Overflow : What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?
这篇关于Excel的表单控件和控件之间的差异ActiveX控件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!