Excel VBA 用户窗体 - 发生变化时执行 Sub

Excel VBA Userform - Execute Sub when something changes

提问by Ashok

I have a userform containing lots of text boxes. When ever the values of these text boxes changes, I need to recalculate my end result values based on the textbox values by calling a subroutine AutoCalc().

我有一个包含大量文本框的用户表单。当这些文本框的值发生变化时，我需要通过调用子例程 AutoCalc() 根据文本框值重新计算最终结果值。

I have around 25 boxes and I don't want to add a Change() event individually to each textbox calling the said subroutine. What's the quickest and efficient way to call the AutoCalc() whenever some value changes?

我有大约 25 个框，我不想将 Change() 事件单独添加到调用所述子例程的每个文本框。每当某些值发生变化时，调用 AutoCalc() 的最快捷有效的方法是什么？

回答by Alex P

This can be achieved by using a class module. In the example that follows I will assume that you already have a userform with some textboxes on it.

这可以通过使用类模块来实现。在接下来的示例中，我将假设您已经有一个带有一些文本框的用户表单。

Firstly, create a class module in your VBA project (let call it clsTextBox-- be sure to change the 'Name' property of the class module!)

首先，在您的 VBA 项目中创建一个类模块（姑且称之为clsTextBox——一定要更改类模块的“名称”属性！）

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Private Sub MyTextBox_Change()
    AutoCalc() //call your AutoCalc sub / function whenever textbox changes
End Sub

Now, in the userform, add the folowing code:

现在，在用户表单中，添加以下代码：

Dim tbCollection As Collection

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control
    Dim obj As clsTextBox

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBox Then
                Set obj = New clsTextBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing

End Sub

回答by Paulo Buchsbaum

The class use, as the answer above suggests, it is a good strategy to deal with many controls in a concise and elegant way, however:

类使用，正如上面的答案所暗示的，以简洁优雅的方式处理许多控件是一个很好的策略，但是：

1) I see no problems in creating 25 events with 1 line, calling a common userform private routine, unless the number of controls is dynamic. It's a KISSphilosophy.

1) 我认为用 1 行创建 25 个事件没有问题，调用公共用户窗体私有例程，除非控件的数量是动态的。这是KISS的哲学。

2) Generally, I consider the Changeevent very disturbing because he does all the recalculation each digit entered. It is more sensible and moderate do this using the Exitevent or Before Updateevent, because it makes the recalculation only when deciding on a value. For instance, The Google Instantannoy me trying to return responses, consuming resources, without the user having defined the question.

2) 一般来说，我认为Change事件非常令人不安，因为他会重新计算输入的每个数字。使用Exit事件或Before Update事件执行此操作更为明智和适度，因为它仅在决定值时进行重新计算。例如，Google Instant 会在用户未定义问题的情况下尝试返回响应、消耗资源，这让我很恼火。

3) There was a validation problem. I agree that you can avoid wrong keys with Changeevent, however if you need to validate the data, you can not know if the user will continue typing or if the data is ready to be validated.

3) 存在验证问题。我同意您可以通过Change事件避免错误的键，但是如果您需要验证数据，您无法知道用户是否会继续输入或数据是否已准备好进行验证。

4) You should remember that Changeor Exitevents does not force the user to pass in text fields, so the system needs to be revalidated and recalculated when trying to exit the form without canceling.

4）你应该记住，Change或Exit事件不会强制用户传入文本字段，所以当试图退出表单而不取消时，系统需要重新验证和重新计算。

The following code is simple but effective for static forms.

以下代码简单但对静态表单有效。

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub
.....
Private Sub TextBox25_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Function Valid
.....
End Function 

Private Sub AutoCalc(Canc As Variant)
If Not Valid() Then Canc=True
'  Calculation
End Sub

It you are addicted to save time, you can create a generic VBA routine in order to generate code for events related to controls in a form that fit a mask. This code can be in a draft sheet (it's safer that generate directly code, that is buggy in some Excel versions) and than copy and paste to a form module.

如果您沉迷于节省时间，您可以创建一个通用的 VBA 例程，以便为与控件相关的事件以适合掩码的形式生成代码。此代码可以在草稿表中（直接生成代码更安全，这在某些 Excel 版本中存在问题），而不是复制并粘贴到表单模块中。

 Sub GenerateEvent(Form As String, Mask As String, _
   Evento As String, Code As String)
 '  Form - Form name in active workbook
 '  Mark - String piece inside control name
 '  Evento - Event name to form procedure name
 '  Code   - Code line inside event
 Dim F As Object
 Dim I As Integer
 Dim L As Long
 Dim R As Range
 Dim Off As Long
 Set F = ThisWorkbook.VBProject.VBComponents(Form)
 Set R = ActiveCell   ' Destination code
 Off = 0
 For I = 0 To F.Designer.Controls.Count - 1
    If F.Designer.Controls(I).Name Like "*" & Mask & "*" Then
        R.Offset(Off, 0) = "Private Sub " & _
          F.Designer.Controls(I).Name & "_" & Evento & "()"
        R.Offset(Off + 1, 0) = "     " & Code
        R.Offset(Off + 2, 0) = "End Sub"
        Off = Off + 4
    End If
 Next I
 End Sub

 Sub Test()
 Call GenerateEvent("FServCons", "tDt", "Exit", _
    "Call AtuaCalc(Cancel)")
 End Sub

回答by Doug Glancy

Take a look at thisfor how to create a class that responds to a change in any textbox. The example is for buttons, but can be modified. However, be aware that Textbox controls don't have an Exit event (that event is actually part of the userform) so you really will have to use the Change event.

看看这个关于如何创建一个响应任何文本框更改的类。该示例用于按钮，但可以修改。但是，请注意 Textbox 控件没有 Exit 事件（该事件实际上是用户窗体的一部分），因此您确实必须使用 Change 事件。

回答by user3164282

I had a similar issue where I want to validate approximately 48 different textboxes using a common routine and the class module approach looked interesting (a lot fewer duplicated lines of code). But I didn't want to validate on every character entered, I only wanted to check after the update. And if the data entered was invalid I wanted to clear the textbox and stay in the same textbox which requires the use of Cancel = True in the Exit routine. After several hours of trying this and not having my AfterUpdate and Exit event handlers never trigger I discovered why.

我有一个类似的问题，我想使用一个通用例程验证大约 48 个不同的文本框，并且类模块方法看起来很有趣（重复的代码行少得多）。但是我不想对输入的每个字符都进行验证，我只想在更新后进行检查。如果输入的数据无效，我想清除文本框并留在需要在退出例程中使用 Cancel = True 的同一个文本框。经过几个小时的尝试并且没有我的 AfterUpdate 和 Exit 事件处理程序从未触发我发现了原因。

If you create a class like the following:

如果您创建一个如下所示的类：

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set** Control(tb As MSForms.TextBox)

    Set MyTextBox = tb

End Property

and then you go into the VBE object browser and select MyTextBox, you will see the enumerated events supported do not include AfterUpdate or Exit. These events are available if you go into the UserForm and use the VBE object browser and look at an instance of a TextBox, but they appear to be inherited from the Controls that the TextBox is a part of. Defining a new class using MSForms.TextBox does not include those events. If you attempt to define those event handlers manually, they will compile and it appears they would work (but they don't). Instead of becoming event handlers of the class object, they will just be private sub routines that show up in (General) under the VBE object browser and never get executed. It appears the only way to create a valid event handler is to select the class object in the VBE object browser and then select the desired event from the enumerated events list.

然后进入 VBE 对象浏览器并选择 MyTextBox，您将看到支持的枚举事件不包括 AfterUpdate 或 Exit。如果您进入 UserForm 并使用 VBE 对象浏览器并查看 TextBox 的实例，则这些事件可用，但它们似乎是从 TextBox 所属的控件继承的。使用 MSForms.TextBox 定义新类不包括这些事件。如果您尝试手动定义这些事件处理程序，它们将被编译并且看起来它们会工作（但它们不会）。它们不会成为类对象的事件处理程序，而只是显示在 VBE 对象浏览器下的 (General) 中并且永远不会执行的私有子例程。

After many hours of searching I've been unable to find any references to show how a similar inheritance model can be constructed within a private class so AfterUpdate and Exit would show up as available events for the created classs. So the recommendation (above) of having a separate event handler for each TextBox on a UserForm, may be the only approach that will work if you want to use AfterUpdate and/or Exit.

经过数小时的搜索，我一直无法找到任何参考资料来说明如何在私有类中构建类似的继承模型，以便 AfterUpdate 和 Exit 将显示为已创建类的可用事件。因此，如果您想使用 AfterUpdate 和/或 Exit，建议（以上）为用户窗体上的每个文本框设置单独的事件处理程序，这可能是唯一可行的方法。

回答by Robert Todar

Two Class Module Method

二类模块方法

I've created a very easy way to add event listeners to a userform. Additionally, it adds events such as MouseOver and MouseOut. (Cool for doing hover effects)

我创建了一种非常简单的方法来向用户表单添加事件侦听器。此外，它还添加了诸如 MouseOver 和 MouseOut 之类的事件。（做悬停效果很酷）

The two class modules that need to be imported in order to work can be found on my Github page VBA Userform Event Listeners

需要导入才能工作的两个类模块可以在我的 Github 页面VBA Userform Event Listeners 上找到

---

---

How to use the code in a Userform

如何在用户表单中使用代码

It's easy to get started, once you add my class modules, simple add the sample code below to a Userform.

上手很容易，一旦你添加了我的类模块，只需将下面的示例代码添加到用户表单中。

Private WithEvents Emitter As EventListnerEmitter

Private Sub UserForm_Activate()
   Set Emitter = New EventListnerEmitter
   Emitter.AddEventListnerAll Me
End Sub

That's it!Now you can start listening for different events.

就是这样！现在您可以开始监听不同的事件。

---

---

The Main Event Listener

主事件监听器

There is the main event EmittedEvent. This passes in the control that the event on, and the event name. So all events go through this event handler.

有主要事件EmittedEvent。这传递了事件发生的控件和事件名称。所以所有事件都通过这个事件处理程序。

Private Sub Emitter_EmittedEvent(Control As Object, ByVal EventName As String, EventValue As Variant)

    If TypeName(Control) = "Textbox" And EventName = "Change" Then
        'DO WHATEVER
    End If

End Sub

---

---

Individual Event Listeners

单个事件侦听器

You can also just listen for the specific events. o in this case the change event.

您也可以只侦听特定事件。o 在这种情况下是更改事件。

Private Sub Emitter_Change(Control As Object)

    If TypeName(Control) = "Textbox" Then
          'DO WHATEVER
    End If

End Sub

Please feel free to check out my Githubpage and make a pull request as not all the events are being captured yet.

请随时查看我的Github页面并提出拉取请求，因为尚未捕获所有事件。

回答by BiggerDon

However, be aware that Textbox controls don't have an Exit event (that event is actually part of the userform) so you really will have to use the Change event.

但是，请注意 Textbox 控件没有 Exit 事件（该事件实际上是用户窗体的一部分），因此您确实必须使用 Change 事件。

I'm confused. Perhaps this was added in 2007, or perhaps I don't understand the nuances. I use the Exit event on TextBox controls. When I Tab out of the control, or click the mouse on another control, it triggers the Exit event.

我糊涂了。也许这是在 2007 年添加的，或者我不明白其中的细微差别。我在 TextBox 控件上使用 Exit 事件。当我 Tab 离开控件，或者在另一个控件上单击鼠标时，它会触发 Exit 事件。

回答by GregNH

So the first 9 lines where given to me in a forum I can't remember where. But I built on that and now I would like to use a command button to re-calculate if the use changes a variable listed in this sub.

所以前 9 行是在论坛中给我的，我不记得在哪里。但是我以此为基础，现在我想使用命令按钮来重新计算使用是否更改了此子中列出的变量。

Private Sub txtWorked_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    11 Dim OTRate       As Double
       OTRate = Me.txtHourlyRate * 1.5
    If Me.txtWorked > 40 Then
       Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * 40, "$#,##0.00")
       Me.txtOvertime = Format((Me.txtWorked - 40) * OTRate, "$#,##0.00")
    Else
       Me.txtOvertime.Value = "0"
       Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * Me.txtWorked.Value, "$#,##0.00")
    End If
    Dim Gross, W2, MASSTax, FICA, Medi, Total, Depends, Feds As Double
       Gross = CDbl(txtBonus.Value) + CDbl(txtBasePay.Value) +    CDbl(txtOvertime.Value)
       W2 = txtClaim * 19
       Me.txtGrossPay.Value = Format(Gross, "$#,##0.00")
       FICA = Gross * 0.062
       Me.txtFICA.Value = Format(FICA, "$#,##0.00")
       Medi = Gross * 0.0145
       Me.txtMedicare.Value = Format(Medi, "$#,##0.00")
       MASSTax = (Gross - (FICA + Medi) - (W2 + 66)) * 0.0545
    If chkMassTax = True Then
       Me.txtMATax.Value = Format(MASSTax, "$#,##0.00")
    Else: Me.txtMATax.Value = "0.00"
    End If
    If Me.txtClaim.Value = 1 Then
       Depends = 76.8

    ElseIf Me.txtClaim.Value = 2 Then
       Depends = 153.8

    ElseIf Me.txtClaim.Value = 3 Then
       Depends = 230.7
    Else
       Depends = 0
    End If
       If (Gross - Depends) < 765 Then
       Feds = ((((Gross - Depends) - 222) * 0.15) + 17.8)
       Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
    ElseIf (Gross - Depends) > 764 Then
       Feds = ((((Gross - Depends) - 764) * 0.25) + 99.1)
       Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
    Else:
       Feds = 0
    End If
       Total = (txtMATax) + (FICA) + (Medi) + (txtAdditional) + (Feds)
       Me.txtTotal.Value = Format(Total, "$#,##0.00")
       Me.txtNetPay.Value = Format(Gross - Total, "$#,##0.00")

End Sub

Private Sub cmdReCalculate_Click()

End Sub