Excel VBA Userform - 当某些更改时执行Sub [英] Excel VBA Userform - Execute Sub when something changes

问题描述

我有一个包含大量文本框的用户表单。当这些文本框的值发生变化时，我需要通过调用子程序AutoCalc（）来重新计算基于文本框值的最终结果值。

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

解决方案

这可以通过使用类模块。在下面的例子中，我将假设您已经有一个用户表单，其中包含一些文本框。

首先，在VBA项目中创建一个类模块（让它调用 clsTextBox - 一定要更改类模块的'Name'属性！）

  Private WithEvents MyTextBox As MSForms.TextBox 
 
公共属性集控制（tb As MSForms.TextBox）
设置MyTextBox = tb 
结束属性
 
 Private Sub MyTextBox_Change（）
 AutoCalc（）//每当文本框更改时调用您的AutoCalc子/函数
 End Sub 
  

现在，在userform中添加以下代码：

  Dim tbCollection As Collection 
 
 Private Sub UserForm_Initialize（）
 Dim ctrl As MSForms.Control 
 Dim obj As clsTextBox 
 
设置tbCollection =新集合
为每个ctrl在Me.Controls 
如果TypeOf ctrl是MSForms.TextBox然后
设置obj =新的clsTextBox 
设置obj.Control = ctrl 
 tbCollection.Add obj 
结束如果
下一个ctrl 
设置obj =没有
 
 End Sub 
  

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().

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?

解决方案

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

这篇关于Excel VBA Userform - 当某些更改时执行Sub的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！