UserForm and Adding up columns

[SailFL]

I have a userform that users enter the names of different areas that will be paved. For each area there is a separate text box the shows the price for the work that will be done for that area. Each price is calculated separately and then there is a total price that is shown on the UserForm.

The problem I am having is how do I know when to call the procedure that will update the prices and the total. There are 9 different area so there are 9 different prices which will be totaled. All the calculations are done in the UserForm.

Thanks

[Zack Barresse]

I think that's ultimately up to you. Sounds like you need to ask yourself, when do you want this total to be 'up to date' or 'summed' up? What should trigger it? A user hitting a button? Automatically upon field update(s)? I would think that it would depend on 1) when/where you would like to see the total, and 2) how close to 'real time' do you want your data? Maybe you could zip/post a sample file?

[SailFL]

I would like the price and totals to update automatically. I don't want the user to have to do anything but enter the data to fill in the form that creates the proposal.

This is the code that updates the total for the nine individual areas.



Sub UpdatePaverInstalPrice()
Dim Count As Integer
For Count = 0 To 8
PaverInstalPrice = PaverInstalPrice + JobArea(Count).Price
Next Count
TextBox141.Text = Format(PaverInstalPrice, "#0.00")
End Sub


Thanks

[Jacob Hilderbrand]

You can call that code from the change event for each control that would effect the total.



Option Explicit

Private Sub TextBox1_Change()
Call UpdatePaverInstalPrice
End Sub

[SailFL]

Okay that makes sense but I did have the call in the place where the individual total was being displayed and the values were not correct. But it could have been the way I am testing. I will add some more code to make the caculations and see what happens.

Thanks

[SailFL]

My calculating code is bad..... If I enter a number in the text box example 123 the value in the total field is 136 because it is totaling as it goes and not when the number is complete. I do not want to add a button. Another way of thinking!

Thanks

[mdmackillop]

Hi Nils,
Can you post a copy of your form layout?

[SailFL]

Malcolm,

I made a beginner programming error.




Sub UpdatePaverInstalPrice()
Dim Count As Integer
' When totaling it some times helps to zero out the variable.
' I needed to add this change since the values are stored in an structure
PaverInstalPrice = 0
For Count = 0 To 8
PaverInstalPrice = PaverInstalPrice + JobArea(Count).Price
Next Count
TextBox141.Text = Format(PaverInstalPrice, "#0.00")
End Sub


Thanks

I have other issues that I will be posting. Many times I answer my own questions and I am learning lots.

[mdmackillop]

I still make them!

You could also consider a label as your output, for a different "appearance"


Label1.Caption = "Total Price: ?" & Format(PaverInstalPrice, "#0.00")

[SailFL]

I also had to add the Call to the procedure to an AfterUpdate() instead of a Change. I want the totals to change after they are done entering the data but not while it is changing. It works very well.