如何在Excel VBA用户窗体中将事件添加到动态创建的控件(按钮,列表框) [英] How to add events to dynamically created controls (buttons, listboxes) in Excel VBA userform
问题描述
我正在尝试实时构建用户表单. (在Excel VBA中)为了进行试用,我从2个标签,一个文本框,一个组合框和一个命令按钮开始.我在下面列出了代码+生成的表单.
I am trying to build a userform in real time. (in Excel VBA) Just to try-out, I started with 2 labels, a textbox, a combobox and a commandbutton. I list the code below + the resulting form.
我的问题是:如何获取链接到控件的事件,特别是组合框和命令按钮?通常(使用手动创建的表单),这将通过名为cmbTabel_change()和cmdExit_click()的例程来完成.但是,当它们动态创建时,这似乎不起作用.谁可以帮助我?
My question is: How can I get events linked to the controls, specifically the combobox and commandbutton? Normally (with manually created forms), this would be done by the routines called: cmbTabel_change() and cmdExit_click(). But this doesn't seem to work, when they are dynamically created. Who can help me?
代码:
Private Sub UserForm_Initialize()
Dim cCont As Control
Call Add_Control(cCont, "Label", "lblDatabase", "Database", 30, 23, 60, 18)
Call Add_Control(cCont, "Textbox", "txtDatabase", "Database", 110, 20, 60, 18)
Call Add_Control(cCont, "Label", "lblTabel", "Tabel", 30, 47, 90, 18)
Call Add_Control(cCont, "Combobox", "cmbTabel", "Tabel", 110, 44, 90, 18)
Call Add_Control(cCont, "CommandButton", "cmdExit", "Afsluiten", 210, 140, 54, 18)
End Sub
Private Sub Add_Control(ctrl, ctp, cnm, cap, l, t, w, h)
Set ctrl = Me.controls.Add("Forms." & ctp & ".1", cnm)
With ctrl
.Left = l
.Top = t
.Width = w
.Height = h
End With
Select Case ctp
Case "Combobox"
controls(cnm).Clear
For j = 1 To 5
controls(cnm).AddItem "ListItem" & j
Next j
controls(cnm).ListIndex = 0
Case "Label", "CommandButton"
With controls(cnm)
.Caption = cap
End With
Case "Textbox"
controls(cnm).Text = cap
End Select
End Sub
结果表格:
推荐答案
您将需要构建一个类来处理控件上的事件.
You'll need to build a class to handle the events on the controls.
作为示例,创建一个名为clsMyEvents
的新类模块.
将此代码添加到类中:
As an example create a new class module called clsMyEvents
.
Add this code to the class:
Option Explicit
Public WithEvents MyCombo As MSForms.ComboBox
Public WithEvents MyButton As MSForms.CommandButton
Private Sub MyCombo_Change()
MsgBox MyCombo.Name & " was changed to value " & MyCombo.Value
End Sub
Private Sub MyButton_Click()
Dim BtnNum As Long
BtnNum = Replace(MyButton.Name, "MyButton", "")
MsgBox MyButton.Name & " is " & IIf(BtnNum Mod 2 = 0, "even", "odd")
End Sub
注意:输入WithEvents
时,您可以选择与该控件类型关联的 most 个事件.
Note: When you enter the WithEvents
you'll be able to select most events associated with that type of control.
接下来,创建一个空白的UserForm
并添加以下代码:
Next, create a blank UserForm
and add this code:
Option Explicit
Public MyEvents As New Collection
Private Sub UserForm_Initialize()
Dim tmpCtrl As Control
Dim CmbEvent As clsMyEvents
Dim x As Long
'Add some dummy data for the combo-boxes.
Sheet1.Range("A1:A5") = Application.Transpose(Array("Red", "Yellow", "Green", "Blue", "Pink"))
Sheet1.Range("B1:B5") = Application.Transpose(Array(1, 2, 3, 4, 5))
Sheet1.Range("C1:C5") = Application.Transpose(Array(5, 4, 3, 2, 1))
For x = 1 To 5
'Add the control.
Set tmpCtrl = Me.Controls.Add("Forms.ComboBox.1", "MyCombobox" & x)
With tmpCtrl
.Left = 10
.Width = 80
.Top = (x * 20) - 18 'You might have to adjust this spacing. I just made it up.
.RowSource = "Sheet1!" & Sheet1.Cells(1, x).Resize(5).Address
End With
'Attach the event.
Set CmbEvent = New clsMyEvents
Set CmbEvent.MyCombo = tmpCtrl
MyEvents.Add CmbEvent
Next x
For x = 1 To 5
Set tmpCtrl = Me.Controls.Add("Forms.CommandButton.1", "MyButton" & x)
With tmpCtrl
.Left = 100
.Width = 50
.Height = 20
.Top = (x * 20) - 18
.Caption = "Num " & x
End With
Set CmbEvent = New clsMyEvents
Set CmbEvent.MyButton = tmpCtrl
MyEvents.Add CmbEvent
Next x
End Sub
编辑:我已经更新为包括命令按钮的代码. 当您在组合框中更改值时,它将告诉您控件的名称以及更改为的值.当您单击一个按钮时,它将告诉您按钮上的数字是奇数还是偶数.
I've updated to include code for a command button as well. When you change the value in a combobox it will tell you the name of the control and the value it was changed to. When you click a button it will tell you if the number on it is odd or even.
这篇关于如何在Excel VBA用户窗体中将事件添加到动态创建的控件(按钮,列表框)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!