Easy task?

[Ethvia]

Hello....I'm trying to cut back on the VBA code I have behind a form. I have about 40 controls on the form that are used to calculate a few text boxes. Currently I have a command button on the form that runs all of the calculations and dumps the results into the other text boxes at the bottom.

I want to have those text boxes update real time. I want the code that I wrote for the command button to run on every other text boxes 'after update' event. I know I can copy/paste the code into each one, but if I have to update the code, then I have to update it in 40 places.

I was wondering if it's possible to create a function (or something) that runs the code and then I just call the function from the 40 text boxes.

the code isn't complicated at all...it's just like:

Text41.value=Text52.value+Text56.value+Text62.Value-Text40.value

Is there a better way than putting that into 40 'after update' events?

thanks

 

[Montez659]

You nailed it when you said create a function and then call it in each after update event. So do that, and then jsut call the function by name:

Private Function CalcTotals()
Me.txtTotal.Value = Me.txt1.value + Me.txt2.Value...
End Function

Private Sub txt1_AfterUpdate()
CalcTotals
End Sub

 

[xenou]

using such a function is a good idea - I've used that strategy too. Not sure but possibly you can avoid code altogether by using an expression in the textbox itself.

E.g., type into the textbox itself as its (control source):
=[Text52]+[Text56]+[Text62]-[Text40]

Might work.

 

[Montez659]

Xen, my experience is that if you do the calculation in the textbox itself, you will still need a Me.Recalc in every After Update event anyways.

On that budgeting database I wrote, I had several textboxes that were dependent on other items, and they always showed up blank unless you hovered over or clicked on them. This was solved by Me.Recalc.