如何在用户窗体上使用带有选项按钮控件的事件 [英] How to use Events with Option Button Controls on Userform

问题描述

我正在尝试从 Excel 工作表的范围中添加一个选项按钮.

对于OptionList中的每个值Set opt = UserForm3.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)opt.Caption = 值opt.Top = opt.Height * iopt.GroupName = "选项"UserForm3.Width = opt.WidthUserForm3.Height = opt.Height * (i + 2)我 = 我 + 1下一个

我想创建一个事件处理程序，以便在用户运行代码时选择 radiobtn1.尽管我得到了很多答案，但这些都是针对工作表用户表单的.

我的目的是处理 VBA 用户表单.请帮助我说出你的想法.

解决方案

^{只有一种类型的用户表单，但是围绕着两种存在[永恒]混淆强大> Excel 可用的控件类型 - 不同在线资源使用的对比术语加剧了这种情况.(只有关于 ActiveX 控件的部分适用于用户表单.)也许我可以通过用有助于我理解的文字来帮助阐明.} ☺

<小时>

概述:

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

  • 两种控件都可用于工作表，但只有 ActiveX 控件可用于用户表单.

  • 表单控件是 Shapes 集合的一部分(就像绘图对象一样)，因此被称为:工作表* **.Shapes("** *<代码>控制名称* **<代码>")**">

  • ActiveX 控件基本上是工作表的一部分，因此被称为:
工作表* **.** *<code>controlname</code>*">

  • 这两种控件都可以从工作表中创建、修改和删除，也可以使用 VBA 以编程方式创建，但是，使用 VBA 引用时，这两种控件的语法略有不同给他们.

• 一些网站还讨论了数据表单.这只不过是一个专门用于数据输入/操作数据的用户表单，因此将它们称为(更熟悉的声音)数据输入"会更有意义用户表单".

• Office 文档有时也将工作表称为表单.虽然这在技术上是正确的，但不要让这让您感到困惑.将形式"一词视为一般意义上的:

<小时>

两种控件

1. 表单控件

2. ActiveX 控件

两者的外观、行为和控制类似，但并不完全相同.(列表 ^{(点击图片放大.)}

☆ _{默认名称"适用于手动创建的控件.以编程方式创建的控件没有(或需要)默认名称，因此应在创建后立即分配一个名称.}

<小时>

^{(来源: 和 (点击图片放大.)}

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

<小时>

ActiveX 控件事件

ActiveX 控件有一个更广泛的事件列表，它们可以设置为响应这些事件.

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

^{(点击图片放大.)}

控制用户表单上的事件处理:

事件也可用于用户窗体的控件中.由于只有 ActiveX 控件可以放置在用户窗体中，因此您将需要进行更多编码 + 更多功能"的权衡.

ActiveX 控件添加到用户表单的方式与添加到工作表的方式相同.请记住，分配给用户表单本身(即背景)的任何事件都将在控件覆盖的任何区域中被阻止"，因此您可能需要将相同的事件分配给控件以及用户表单.

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

<小时>

VBA 示例

使用 VBA 添加/修改/删除表单控件选项按钮:

子表单Control_add()'创建表单控件将 ws 调暗为工作表:设置 ws = ActiveSheet使用 ws.Shapes.AddFormControl(xlOptionButton, 25, 25, 100, 100).Name = "cOptionButton1" '立即命名控件(以便我们稍后找到它)结束于结束子子 formControl_modify()'修改表单控件的属性将 ws 调暗为工作表:设置 ws = ActiveSheetws.Shapes("cOptionButton1").SelectWith Selection '形状必须在更改之前选择.Characters.Text = "wxyzabcd"结束于结束子子窗体Control_delete()'删除表单控件将 ws 调暗为工作表:设置 ws = ActiveSheetws.Shapes("cOptionButton1").删除结束子

• 形状.AddShape 方法 (Excel)

• 形状属性 (Excel)

• 字符对象 (Excel)

<小时>

使用 VBA 添加/修改/删除 ActiveX 命令按钮:

Sub activexControl_add()'创建 ActiveX 控件将 ws 调暗为工作表:设置 ws = ActiveSheet使用 ws.OLEObjects.Add("Forms.CommandButton.1").左 = 25.Top = 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").删除结束子

• OLE 对象.添加方法 (Excel)

• 背景色、前景色属性(ActiveX 控件)

<小时>

从表单控件组合框中添加/删除项目:

Sub ComboBox_addRemoveItems_FormControl()将 ws 调暗为工作表:设置 ws = ActiveSheet'将项目添加到表单控件组合框ActiveWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem "abcd"'从表单控件组合bo中删除所有项目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:添加复选框或选项按钮(表单控件)

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

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

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

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

• MSDN:VBA 形状成员

• MSDN:在表格上使用 ActiveX 控件(Office)

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

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

• 微软技术网:嵌入在 Office 文档中的 ActiveX 控件的行为

I am trying to add an option button from the range in the Excel worksheet.

For Each Value In OptionList

    Set opt = UserForm3.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)
    opt.Caption = Value
    opt.Top = opt.Height * i
    opt.GroupName = "Options"

    UserForm3.Width = opt.Width
    UserForm3.Height = opt.Height * (i + 2)

    i = i + 1

Next

I want to create an event handler so that if radiobtn1 is selected while running the code from the user. Alhough I got a lot of answers, those are meant for worksheet user form.

My intention is to work on the VBA user form. Please help me with your thoughts.

解决方案

^{There is only one type of userform, however there is [eternal] confusion surrounding the two types of controls available to Excel — exacerbated by the contrasting terminology used by different online sources. (Only the sections about ActiveX controls apply to userforms.) Perhaps I can help shed some light by putting it in words that help me understand.} ☺

---

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

1. Form Controls

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

---

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

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

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

• Shapes.AddShape Method (Excel)

• Shape Properties (Excel)

• Characters Object (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)

• BackColor, ForeColor Properties (ActiveX Controls)

---

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

• MSDN : Using ActiveX Controls on Sheets (Office)

• 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

这篇关于如何在用户窗体上使用带有选项按钮控件的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！