当前位置：文江博客话题详情

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

发布于 2024-11-06 04:20:12 字数 166 浏览 0 评论 0原文

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

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧时模样 2024-11-13 04:20:12

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

首先，在您的 VBA 项目中创建一个类模块（我们将其命名为 clsTextBox - 请务必更改该类模块的“Name”属性！）

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

现在，在用户窗体中添加以下代码：

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

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!)

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

回复收藏 0 原文

定格我的天空 2024-11-13 04:20:12

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

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

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

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

4）您应该记住，Change或Exit事件不会强制用户传入文本字段，因此在尝试退出表单时系统需要重新验证和重新计算无需取消。

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

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

如果您想节省时间，可以创建通用 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

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 KISS philosophy.

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

3) There was a validation problem. I agree that you can avoid wrong keys with Change event, 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.

4) You should remember that Change or Exit events 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.

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.

 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

回复收藏 0 原文

撑一把青伞 2024-11-13 04:20:12

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

回复收藏 0 原文

一笔一画续写前缘 2024-11-13 04:20:12

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

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

Private WithEvents MyTextBox As MSForms.TextBox

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

    Set MyTextBox = tb

End Property

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

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

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.

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.

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.

回复收藏 0 原文

魄砕の薆 2024-11-13 04:20:12

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

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

回复收藏 0 原文

星星的轨迹 2024-11-13 04:20:12

两个类模块方法

我创建了一种非常简单的方法来将事件侦听器添加到用户窗体。此外，它还添加了 MouseOver 和 MouseOut 等事件。（很适合做悬停效果）

需要导入才能工作的两个类模块可以在我的 Github 页面上找到 VBA 用户窗体事件监听器

如何在用户窗体中使用代码

上手很容易，添加我的类模块后，只需将下面的示例代码添加到用户窗体即可。

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

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

主事件监听器

有一个主事件EmissedEvent。这会传入事件所在的控件以及事件名称。所以所有事件都会经过这个事件处理程序。

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

单个事件侦听器

您也可以只侦听特定事件。 o 在本例中为更改事件。

Private Sub Emitter_Change(Control As Object)

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

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

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)

The two class modules that need to be imported in order to work can be found on my Github page 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.

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.

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 Github page and make a pull request as not all the events are being captured yet.

回复收藏 0 原文

风渺 2024-11-13 04:20:12

所以前 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

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.

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

回复收藏 0 原文

~没有更多了~