Userform - event change for multiple textbox

Question

I try to update some lable value for each textbox change. i searched and found this link and this link in website, but i still having trouble adopt it in my code. Because i have no idea how "class" work.

Code in Class named "QuoTxtBoxEvt":

Option Explicit

Private WithEvents TxtBoxGroup As MSForms.textbox

Public Sub TxtBoxGroup_Change()
    With frmQuo
        .TextBox28.value = Val(.TextBox8.value) * Val(.TextBox9.value)
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox11.value) * Val(.TextBox12.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox14.value) * Val(.TextBox15.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox17.value) * Val(.TextBox18.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox20.value) * Val(.TextBox21.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox23.value) * Val(.TextBox23.value))
        If .CheckBox1 = True Then .TextBox28.value = Val(.TextBox28.value) * (100 - Val(.TextBox26.value)) / 100
        If .CheckBox2 = True Then .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox28.value) * Val(.TextBox27.value) / 100)
    End With
End Sub

Code in userform:

Private Sub UserForm_Initialize()
'Variables for textbox change event handler
Dim TxtBoxGroupEventHandler() As New QuoTxtBoxEvt
Dim vfrmControl As Control
Dim i As Integer

'Event handler for textbox change to update total cost
i = 1
For Each vfrmControl In Me.Controls
    If TypeName(vfrmControl) = "TextBox" Then
        ReDim Preserve TxtBoxGroupEventHandler(1 To i)
        Set TxtBoxGroupEventHandler(i).TxtBoxGroup = vfrmControl
        i = 1 + i
    End If
Next vfrmControl
End Sub

These my code so far, and it return an error. What i miss in my code? Can someone briefly teach me how to code "class"?

Many thank Orz

Edit: I got another method to achieve this by create a sub that update my value and call that sub in each textbox change event. But is this best way(correct way?) to do it? or using the class method is better?

question: if you added a button, once the user clicks on it, it updates the values to all lables acoording to the `TextBoxes` related to them, is it OK? it will simplify your code a lot ? Unless you must have it every time one of the `TextBoxes` change — Shai Rado, Jul 20 '16 at 06:23

score 0 · Accepted Answer · 2016-07-20T08:10:55.137

When grouping events we use a class to hold a reference to a control. Next we create a new instance of the class for each control and add it to a collection or array variable. The collection or array variable needs to be declared at the module level. In this way the references to the controls are keep alive in code after the initializing sub routine has finished executing.

It looks like you are only going to need the event to fire when the value of .TextBox8.value or .TextBox9.value is changed. For this reason, I wouldn't have went through the extra setup.

Private Sub TextBox8_Change()
    RecalcTextBoxes
End Sub

Private Sub TextBox9_Change()
    RecalcTextBoxes
End Sub

Sub RecalcTextBoxes()
    With frmQuo
        TextBox28.Value = Val(TextBox8.Value) * Val(TextBox9.Value)
        TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox11.Value) * Val(TextBox12.Value))
        TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox14.Value) * Val(TextBox15.Value))
        TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox17.Value) * Val(TextBox18.Value))
        TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox20.Value) * Val(TextBox21.Value))
        TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox23.Value) * Val(TextBox23.Value))
        If CheckBox1 = True Then TextBox28.Value = Val(TextBox28.Value) * (100 - Val(TextBox26.Value)) / 100
        If CheckBox2 = True Then TextBox28.Value = Val(TextBox28.Value) + (Val(TextBox28.Value) * Val(TextBox27.Value) / 100)
    End With
End Sub

You should add a flag to your QuoTxtBoxEvt class that will prevent the TxtBoxGroup_Change event from retriggering itself. The way it stands right now, you are changing the value of TextBox28 8 times. That means you are calling the TxtBoxGroup_Change event 8 times, which triggers 8 * 8 more calls and so on and so on.

Option Explicit

Private WithEvents TxtBoxGroup As MSForms.TextBox
Private EditMode As Boolean

Public Sub TxtBoxGroup_Change()

    If EditMode Then 
       Exit Sub
    Else
       EditMode = True
    End If

    With frmQuo
        .TextBox28.value = Val(.TextBox8.value) * Val(.TextBox9.value)
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox11.value) * Val(.TextBox12.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox14.value) * Val(.TextBox15.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox17.value) * Val(.TextBox18.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox20.value) * Val(.TextBox21.value))
        .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox23.value) * Val(.TextBox23.value))
        If .CheckBox1 = True Then .TextBox28.value = Val(.TextBox28.value) * (100 - Val(.TextBox26.value)) / 100
        If .CheckBox2 = True Then .TextBox28.value = Val(.TextBox28.value) + (Val(.TextBox28.value) * Val(.TextBox27.value) / 100)
    End With

    EditMode = False

End Sub

Thomas, thank for you answer, but your answer look like wiki to me. I need some time to disguise. ^^Cheer — KokJoo89, Jul 20 '16 at 07:35
In my case, i need to keep track more then 2 textbox change_event (all the textbox i write inside the class, that around 16), which currently i failed to write class to make it work. So i take the 1st method you mention(create sub) where i had to add to 16 textbox change_event. I do tried to name my controls to meaningful name, but i find out i might the name as it is for looping. Can i still able to loop those control after i rename them to unique name? — KokJoo89, Jul 20 '16 at 07:46
It turned into more of a code review. I had an error in `QuoTxtBoxEvt ` class. When working with events be careful not to re-trigger the event. — , Jul 20 '16 at 07:48
If you can provide a download like, I will refactor it for you. — , Jul 20 '16 at 07:50
Hi Thomas, i edited my class code in main post. that my typo error in class calculate. — KokJoo89, Jul 20 '16 at 07:59
I updated my answer. You really should give those textboxes meaningful names (e.g. txtCost , txtQuantity) — , Jul 20 '16 at 08:16

Userform - event change for multiple textbox

1 Answers1