VBA Excel stop userform events from class module

Question

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

Answer 1

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