Excel中的表单控件和ActiveX控件之间的差异概述。

Why are there 2 types of controls available in Excel? (2 buttons, 2 combo boxes, 2 check box, etc...)

为什么Excel中有两种控件?(2个按钮、2个组合框、2个复选框等)

What's the difference between Forms Controls and ActiveX Controls? Which one should I use?

窗体控件和ActiveX控件的区别是什么?我应该用哪个?

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?

我如何处理每种类型，以及如何区分它们的区别?

1 个解决方案

#1

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. ☺

这只是对表单控件和ActiveX控件之间的差异的概述(基于我的旧笔记，它帮助我最终找到了差异!)访问包含的链接以获得关于每个控件的详细信息，包括代码和示例项目。☺

^{(Adapted from my original post here — now closed)}

(改编自我原来的职位-现已关闭)

---

Overview:

• There are two types of controls: Form controls and ActiveX controls:

  有两种类型的控件:表单控件和ActiveX控件:

  • Both types of controls can be used on worksheets but only ActiveX controls can be used on userforms.

    这两种控件都可以在工作表中使用，但是只有ActiveX控件可以在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:
    

    ActiveX控件基本上是工作表的一部分，因此被称为:

  • 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.

    可以从工作表中创建、修改和删除这两种类型的控件，也可以使用VBA以编程的方式创建、修改和删除它们。

• 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".

  有些网站也讨论数据形式。这只不过是专门为数据输入/数据操作而设计的userform，因此调用它们(听起来更熟悉)会更有意义“数据输入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

1. Form Controls

   表单控件

2. ActiveX Controls

   ActiveX控件

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":

例如，让我们比较这两种组合框。在某些编程语言中，类似的控件被称为“下拉菜单”或“下拉列表”。在Excel中，我们有一个“表单控件组合框”和一个“ActiveX控件组合框”:

^{(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

  Office.com:工作表上表单、表单控件和ActiveX控件的概述

• MSDN : Add Controls to a User Form

  MSDN:向用户表单添加控件

---

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.}

ActiveX控件是VBA工具箱的扩展。使用ActiveX控件就像使用任何标准的内置控件一样，比如复选框控件。当向应用程序添加ActiveX控件时，它将成为开发和运行时环境的一部分，并为应用程序提供新的功能。

• ^{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.}

  ActiveX控件实现为一个进程内服务器(通常是一个小对象)，可以在任何OLE容器中使用。请注意，ActiveX控件的全部功能只有在设计为了解ActiveX控件的OLE容器中使用时才可用。

• ^{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:}

  这种容器类型称为控件容器或控件对象，可以使用控件的属性和方法来操作ActiveX控件，并以事件的形式接收ActiveX控件的通知。下图展示了这种交互作用:

  
  _{^{(Source: this and this)}}

  (来源:这个和这个)

See also:

参见:

• Wikipedia: ActiveX

  *:ActiveX

• Symantec.com : Discussion of ActiveX Vulnerabilities

  Symantec.com:关于ActiveX漏洞的讨论

• How-To Geek : What ActiveX Controls Are and Why They’re Dangerous

  操作指南:什么是ActiveX控件，为什么它们是危险的

---

Option Buttons (Radio Buttons)

In Excel, the two types of radio buttons are actually called Option Buttons. To further confuse matters:

在Excel中，这两种单选按钮实际上被称为选项按钮。让事情更为混乱的是:

• the default name for the form control is OptionButton1.

  表单控件的默认名称是OptionButton1。

• the default name for the ActiveX control is Option Button 1.

  ActiveX控件的默认名称是选项按钮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.

区分它们的一种好方法是打开控件的属性列表(在Development选项卡下的功能区上，或者右键单击控件并选择属性，或者单击F4)，因为ActiveX控件拥有比更简单的表单控件更多的选项。

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.

选择group box控件，然后按住Ctrl，同时选择要分组的其他控件。右键单击组框控件,选择分组→组。

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.

表单控件事件只能响应一个事件:单击事件。(更多信息。)注意，这一节不适用于userforms，因为它们只使用ActiveX控件。

To add a procedure for the Click event:

为点击事件添加一个过程:

• Right-click the control and choose Assign Macro...

  右键单击控件并选择Assign宏…

• In the 'Assign Macro` Dialog:

  在“分配宏”对话框中:

  • Select an existing procedure, and click OK, or,

    选择一个现有的过程，然后单击OK，或者，

  • Create a new procedure in the VBE by clicking New..., or,

    单击new…在VBE中创建一个新过程。,或者,

  • Record a new macro by clicking Record..., or,

    单击Record…记录一个新的宏。,或者,

  • to Remove the assigned event, delete its name from Macro Name field and click OK.

    要删除所分配的事件，请从宏名称字段中删除它的名称并单击OK。

  ^{(Click image to enlarge.)}

  (点击图片放大)。

To rename, edit or delete existing macros, hit Alt+F8 to open the Macro interface:

要重命名、编辑或删除现有宏，按Alt+F8打开宏界面:

---

ActiveX control events

ActiveX controls have a more extensive list of events to which they can be set up to respond.

ActiveX控件有一个更广泛的事件列表，可以对其进行设置以进行响应。

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.

要将事件分配给ActiveX控件，请右键单击该控件并选择View代码。在VBE中，您可以粘贴代码，或者从VBE窗口右上角的下拉列表中选择特定的事件。

^{(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控件可以被放置一个用户表单，所以您将有“更多的编码+更多功能”的权衡。

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.

在用户表单中添加ActiveX控件的方式与添加到工作表中的方法相同。请记住，任何分配给userform本身的事件(即。在控件覆盖的任何区域中，都将被“阻塞”，因此您可能需要将相同的事件分配给控件和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:

例如，为了让这个userform响应MouseMove，在表单的任何地方，用户表单、文本框、选项按钮和框架都应用了相同的事件代码:

---

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

• Shapes.AddShape Method (Excel)

  形状。AddShape方法(Excel)

• Shape Properties (Excel)

  形状属性(Excel)

• Characters Object (Excel)

  字符对象(Excel)

---

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

• OLEObjects.Add Method (Excel)

  OLEObjects。添加方法(Excel)

• BackColor, ForeColor Properties (ActiveX Controls)

  反色、预着色属性(ActiveX控件)

---

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:添加复选框或选项按钮(表单控件)

• Office.com : Add a checkbox, option button, or toggle button (ActiveX controls)

  Office.com:添加复选框、选项按钮或切换按钮(ActiveX控件)

• Office.com : Overview of forms, Form controls, and ActiveX controls on a worksheet

  Office.com:工作表上表单、表单控件和ActiveX控件的概述

• Office.com : Enable selection through choice controls (check and list boxes)

  Office.com:通过选择控件启用选择(检查和列表框)

• Office.com : Add, edit, find, and delete rows by using a data form

  Office.com:使用数据表单添加、编辑、查找和删除行

• MSDN : VBA Shape Members

  VBA形状成员

• MSDN : Using ActiveX Controls on Sheets (Office)

  MSDN:使用ActiveX控件(Office)

• Exceldemy : How to Use Form Controls in Excel

  Exceldemy:如何在Excel中使用表单控件

• MSDN : Using Windows Forms Controls on Excel Worksheets (Visual Studio)

  MSDN:在Excel工作表上使用Windows窗体控件(Visual Studio)

• Microsoft TechNet : Behaviour of ActiveX controls embedded in Office documents

  Microsoft TechNet: ActiveX控件嵌入到Office文档中的行为

• Stack Overflow : What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?

  栈溢出:Excel 2010中的“表单控件”和“ActiveX控件”有什么区别?