Change event for several Textboxes on UserForm

[RyanH]

I have a Userform with about 35 Textboxes. I have 3 other Textboxes that
show calculation results from those 35 textboxes. I want those 3 textboxes
to refresh there results whenever any of the 35 textboxes are changed.
Instead of putting a Change Event in every single textbox is there a shorter,
quicker way of doing this?

 

[Bob Phillips]

Simplest way is to have a simple sub to add them

Private Function SumValues()
Dim i As Long

With Me

.txtSum1.Text = Val(.TextBox1.Text) + Val(.TextBox2.Text)

.txtSum2.Text = Val(.TextBox1.Text) - Val(.TextBox12.Text) +
Val(.TextBox19.Text)

.txtSum3.Text = Val(.TextBox1.Text) * Val(.TextBox2.Text)
End With
End Function

Obviously you would need to change it to the correct sum calulations.

Then call it from each of the 35 textboxes like so


Private Sub TextBox1_AfterUpdate()
Call SumValues
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RyanH]

Thanks for replying so quickly. I failed to mention that I have a
CommandButton_Click Event that contains all my code for the calculations. I
am new to VBA so I could be wrong, but I think your function idea is
essentially the same thing, right? Your way may be better because I would
not have to have a command button to do the calculations, right?

I was trying to avoid having to call the CommandButton_Click Event or
Function from each and every TextBox. Is there a simply loop that can be
called if any of the TextBoxes are changed on the UserForm?

 

[Bob Phillips]

You could try this approach, but it is more setup.

It also fires the summation code after every change in the textbox, not
after the update, as this method doesn't support AfterUpdate

In the userform

Option Explicit

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cTBEvents As clsUserFormEvents
Dim ctl As MSForms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
Set cTBEvents = New clsUserFormEvents
Set cTBEvents.mTBGroup = ctl
mcolEvents.Add cTBEvents
End If
Next

End Sub

and add a class module called clsUserFormEvents with this code

Option Explicit

Public WithEvents mTBGroup As MSForms.TextBox

Private Sub mTBGroup_Change()
'your summation code in here
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RyanH]

Wow, that particular code is a bit over my head and I could possibly have
issues debugging it if a problem should arrised in the future from it. I
appreciate your responses. I will use your function idea, remove my
CalculateButton (frees up space, which I need, sweet!), and just call the
function from each Textbox_Change Event.

Thanks for your help!
Ryan