英文:
VBA Excel stop userform events from class module
问题
我已经动态填充了用户窗体中的文本框。我有一个类,用于为这些文本框设置事件。目前有两个文本框,比如说一个预算 - 预算作为百分比和预算作为绝对数值,都是可编辑的。我的目标是允许用户同时更改它们,以使它们相互更改。
我卡在一个阶段,当我基于另一个值更改一个文本框时,它会再次触发更改事件,从而创建无限循环。我正在寻找一种在我更改这两个文本框的值的阶段在类模块中禁用更改事件的方法。
请问您有什么最合适的方法来做到这一点吗?谢谢!
Private WithEvents txtbox As MSForms.TextBox
Public Property Set TextBox(ByVal t As MSForms.TextBox)
Set txtbox = t
End Property
Private Sub txtbox_Change()
req = Left(txtbox.name, 2)
fc = ContractView.ContractsFYPHeaderFrm.Controls(req & "ccsfc").Caption
If Right(txtbox.name, 3) = "pfc" Then
orig_val = txtbox.Value
Else
orig_val = txtbox.Value / fc
End If
Dim str As String
Dim n As Long
Dim vfyp As Object: Set vfyp = ContractView.ContractsFYPValFrm
Dim hfyp As Object: Set hfyp = ContractView.ContractsFYPHeaderFrm
Dim rbf As Object: Set rbf = ContractView.RequestsBudgetFrm
Dim c As Control
On Error Resume Next
ri = CInt(Right(req, 1))
str = Replace(txtbox.name, req, "")
budget = Left(str, Len(str) - 5)
dtype = Right(txtbox.name, 5)
If Right(txtbox.name, 3) = "pfc" Then
vfyp.Controls(req & budget & "ccafc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, PLSubtotalFormat) 'budget investments abs
Else
vfyp.Controls(req & budget & "ccpfc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, ContractPrct) 'budget investments prct
End If
End Sub
希望这能帮助您解决问题。
英文:
I've dynamically populated userform with textboxes. I've class which sets events for these textboxes. At the moment there are 2 textboxes for one let's say budget - budget as % and budget as absolute number, both editable. My target is to allow user change them both resulting them in changing each other
I'm stuck at stage where I change 1 textbox based on another value it triggers change event again and creates infinite loop. I'm looking for a way to disable change event in class module for the stage where I change values in these 2 textboxes
Could you please advise what's the most appropriate way to do this. Thank you!
Private WithEvents txtbox As MSForms.TextBox
Public Property Set TextBox(ByVal t As MSForms.TextBox)
Set txtbox = t
End Property
Private Sub txtbox_Change()
req = Left(txtbox.name, 2)
fc = ContractView.ContractsFYPHeaderFrm.Controls(req & "ccsfc").Caption
If Right(txtbox.name, 3) = "pfc" Then
orig_val = txtbox.Value
Else
orig_val = txtbox.Value / fc
End If
Dim str As String
Dim n As Long
Dim vfyp As Object: Set vfyp = ContractView.ContractsFYPValFrm
Dim hfyp As Object: Set hfyp = ContractView.ContractsFYPHeaderFrm
Dim rbf As Object: Set rbf = ContractView.RequestsBudgetFrm
Dim c As Control
On Error Resume Next
ri = CInt(Right(req, 1))
str = Replace(txtbox.name, req, "")
budget = Left(str, Len(str) - 5)
dtype = Right(txtbox.name, 5)
If Right(txtbox.name, 3) = "pfc" Then
vfyp.Controls(req & budget & "ccafc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, PLSubtotalFormat) 'budget investments abs
Else
vfyp.Controls(req & budget & "ccpfc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, ContractPrct) 'budget investments prct
End If
End Sub
答案1
得分: 1
以下是翻译的代码部分:
找到以下解决方案:
1. 创建一个全局布尔变量,用于定义是否需要触发更改事件。例如:`Global ClassDisableEvents As Boolean`
2. 在用户窗体模块中的`userform_initialize`事件中设置为允许初始更改事件:`ClassDisableEvents = False`
3. 结构化你的类模块事件代码如下:
以`If ClassDisableEvents = True Then Exit Sub`开头,这样你就不会浪费时间无谓地运行更改事件
你的事件代码
在设置文本框的值之前,加入`ClassDisableEvents = True`,文本框在集合中具有`textbox_change`事件,执行文本框的操作
最后加上`ClassDisableEvents = False`
以下是我的类事件模块示例:
```vb
Private WithEvents txtbox As MSForms.TextBox
Public Property Set TextBox(ByVal t As MSForms.TextBox)
Set txtbox = t
End Property
Private Sub txtbox_Change()
If ClassDisableEvents = True Then Exit Sub
req = Left(txtbox.name, 2)
fc = val(ContractView.ContractsFYPHeaderFrm.Controls(req & "ccsfc").Caption)
If Right(txtbox.name, 3) = "pfc" Then
orig_val = txtbox.Value
Else
orig_val = txtbox.Value / fc
End If
.
.
你的事件代码
.
.
ClassDisableEvents = True
If Right(txtbox.name, 3) = "pfc" Then
vfyp.Controls(req & budget & "ccafc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, PLSubtotalFormat) 'budget investments abs
Else
vfyp.Controls(req & budget & "ccpfc").Value = VBA.Format(orig_val, ContractPrct) 'budget investments prct
End If
ClassDisableEvents = False
End Sub
英文:
Found following solution:
-
Create global boolean variable used to define if you need to trigger change event or not. For example:
Global ClassDisableEvents As Boolean
-
Place in your userform module on userform_initialize event to allow initial change events
ClassDisableEvents = False
-
Structure your class module event code the following way:
Start with
If ClassDisableEvents = True Then Exit Sub
so you
don't waste time running change event for nothingYour event code
Place
ClassDisableEvents = True
before you set value to textbox
which has textbox_change event in collection Perform actions with
textboxClose it out with
ClassDisableEvents = False
Here's my example of class event module:
Private WithEvents txtbox As MSForms.TextBox
Public Property Set TextBox(ByVal t As MSForms.TextBox)
Set txtbox = t
End Property
Private Sub txtbox_Change()
If ClassDisableEvents = True Then Exit Sub
req = Left(txtbox.name, 2)
fc = val(ContractView.ContractsFYPHeaderFrm.Controls(req & "ccsfc").Caption)
If Right(txtbox.name, 3) = "pfc" Then
orig_val = txtbox.Value
Else
orig_val = txtbox.Value / fc
End If
.
.
your event code
.
.
ClassDisableEvents = True
If Right(txtbox.name, 3) = "pfc" Then
vfyp.Controls(req & budget & "ccafc").Value = VBA.Format(orig_val * hfyp.Controls(req & "ccsfc").Caption, PLSubtotalFormat) 'budget investments abs
Else
vfyp.Controls(req & budget & "ccpfc").Value = VBA.Format(orig_val, ContractPrct) 'budget investments prct
End If
ClassDisableEvents = False
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论