Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sum values entered into text boxes

  Asked By: Jason    Date: Dec 21    Category: MS Office    Views: 1974
  

I'm relatively new to VBA, even more so with UserForms and I need
some help. I've put together a form that has 9 textboxes
(named "txtBalYards", "txtOpt1Yards", "txtOpt2Yards",...,"txtOpt8Yards
"), and I'm trying to figure out how to sum the values and have the
value display in a separate textbox (named "txtTotalYards"). The
code below doesn't produce any errors, but it doesn't actually do
anything (i.e. it doesn't display the sum of the values in the text
box on the userform). My questions are these: 1) Does the code itself
look like it should work? and 2) Do I have to put anything in the
cmdCalculate_Click() routine so that it will call on the values
calculated in this section of code.

Private Sub txtTotalYards_Change()
Dim TotalYards As Integer
TotalYards = 0
For i = 1 To 8
If Me("txtOpt" & i & "Yards").Text <> "" Then
TotalYards = TotalYards + _
Me.txtBalYards.Text + _
Me("txtOpt" & i & "Yards").Text
End If

Next i
Me.txtTotalYards.Text = TotalYards
End Sub

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Dalpat Student     Answered On: Dec 21

The problem is that you've assigned the sub to the TOTAL textbox, so
that when the TOTAL changes, then calculate the TOTAL... not exactly
what you're wanting.
What I would do, is rename your Private sub to Sub Calc_Total() (or
something like that) and move it to a Module.
Then, for each of your textboxes, create a change event to call  the
new sub, like:
Private Sub txtOpt1Yards_Change()
Calc_Total
End Sub
Private Sub txtOpt2Yards_Change()
Calc_Total
End Sub

then, whenever any of the textbox values  change, the total gets
re-calcualted.

Warning: the change event is called for EACH KEYSTROKE
That means that if you already have 100 and 200 in boxes  1 and 2,
then plan to enter the value of 224 into the
third textbox: when you hit 2, the total will update to 302,
then you hit another 2 (for 22), the total will update to 322.
then you hit the final number (4) the total will update to 524.

If this is adequate, then you're good to go.
If not, you might consider using the Exit event, instead of the
Change event.
With the Exit event, the total will be updated when the user moves
the type-in bar to another box.

 
Answer #2    Answered By: Priscilla Lewis     Answered On: Dec 21

I will play around with your suggestions to see if I
can get additional functionality out of the user form. For now, I
have it working by setting the value of a variable inside the Change
event, and then calling upon that value in the main procedure. Like
such:

Private Sub txtTotalYards_Change()
> > Dim TotalYards As Integer
> > TotalYards = 0
> > For i = 1 To 8
> > If Me("txtOpt" & i & "Yards").Text <> "" Then
> > TotalYards = TotalYards + _
> > Me.txtBalYards.Text + _
> > Me("txtOpt" & i & "Yards").Text
> > End If
> >
> > Next i
> > Me.txtTotalYards.Text = TotalYards
> > End Sub

and then in the main procedure:

Private Sub cmdCalculate_Click()
[other code]
Me.txtTotalYards.Value = TotalYards
End Sub

I'm sure that there's a better way to do it, and I'll continue
polishing my VBA with suggestions like yours, but for now, it works,
and I'm satisfied. Thanks again for your suggestions.

 
Answer #3    Answered By: Delbert Cooper     Answered On: Dec 21

I'm suspicious of one thing though.
You seem to be putting this calculation in the change event for the TOTAL.
Yet, the TOTAL doesn't get changed until you manually change the TOTAL.

Do me a favor... put  a msgbox in the total change event.
Then change one of the other values  and see how many times the Total change
event is being called.
For what you describe, there really shouldn't BE any change event
associated
with the Total textbox, there should only be change events with the other
textboxes.

Just make sure it's being triggered when you want and expect it to, not
unpredictably. Otherwise, you could end up with incorrect results.

 
Didn't find what you were looking for? Find more on Sum values entered into text boxes Or get search suggestion and latest updates.




Tagged: