VBA Excel停止用户窗体事件来自类模块。

huangapple go评论111阅读模式
英文:

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:

  1. Create global boolean variable used to define if you need to trigger change event or not. For example: Global ClassDisableEvents As Boolean

  2. Place in your userform module on userform_initialize event to allow initial change events ClassDisableEvents = False

  3. 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 nothing

    Your event code

    Place ClassDisableEvents = True before you set value to textbox
    which has textbox_change event in collection Perform actions with
    textbox

    Close 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

huangapple
  • 本文由 发表于 2023年6月19日 15:35:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76504521.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定