Textboxes, chage event and math

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
Hello every one! I have 6 textboxes and the first 5 boxes need to be added together and in the sixth textbox is where the answer will be. My problem is I am using afterupdate and it works fine but only for one textbox. Once I move out of the next textbox it does not add up all 5 textboxes. Here is a sample of the code. I hope someone can help, I know it is something dumb I am doing but I cant figure out.




Code:
Private Sub TextBox6_afterupdate()
    If TextBox6.Value <> "" Then
        TextBox11.Value = Format(Val(TextBox6.Value) + Val(TextBox7.Value) + Val(TextBox8.Value) + Val(TextBox9.Value) _
        + Val(TextBox10.Value), "$#,##0.00")
    End If
    
    TextBox6.Value = FormatCurrency(TextBox6.Value)
End Sub

Private Sub TextBox7_afterupdate()
    If TextBox7.Value <> "" Then
        TextBox11.Value = Format(Val(TextBox6.Value) + Val(TextBox7.Value) + Val(TextBox8.Value) + Val(TextBox9.Value) _
        + Val(TextBox10.Value), "$#,##0.00")
    End If

    TextBox7.Value = FormatCurrency(TextBox7.Value)
    
       
End Sub
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
  1. AfterUpdate only fires if you change something. Tabbing through it will not invoke the event handler. If you want to invoke the code every time you hop, skip or tab through it, use _Exit.
  2. Why not move the summing up bit into its own separate private little SUB? That way you don't have to worry about riding herd on five copies of the same code? Each of the posse of textboxes can just call the wee sum sub.
HTH
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
Hi Greg!

Thanks for the help. I am aware of how afterupdate works but i thought since i have it for all 5 textboxes it will always add up the boxes even if they were updated before the last.

I do not know how to accomplish number 2 of your post. I have an idea of what you mean but by no means do i have the know how to accomplish such a thing.

I will give the exit a try. thanks so very much
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
Hi there!

I changed the code as you mentioned but I am still getting the same problem. that is if textbox6 has $50.00 entered into it then textbox11 will equals $50.00 but then when textbox7 has $100.00 entered into it, textbox11 should now say $150.00 but it still only shows $50.00. Thanks for any help.

Code:
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox6.Value <> "" Then
        TextBox11.Value = Format(Val(TextBox6.Value) + Val(TextBox7.Value) + Val(TextBox8.Value) + Val(TextBox9.Value) _
        + Val(TextBox10.Value), "$#,##0.00")
    End If
    
    TextBox6.Value = FormatCurrency(TextBox6.Value)
End Sub

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox7.Value <> "" Then
        TextBox11.Value = Format(Val(TextBox6.Value) + Val(TextBox7.Value) + Val(TextBox8.Value) + Val(TextBox9.Value) _
        + Val(TextBox10.Value), "$#,##0.00")
    End If

    TextBox7.Value = FormatCurrency(TextBox7.Value)
    
       
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you actually entering the $ signs?

If you are then Val might be returning 0 values.
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
I am not entering any $ signs but the formatting is...should I take it out?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes.
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
OK, I did some changing around and now it is doing some addition but it is not adding correctly for example if I place 10,000 in textbox6 and 5,000 in textbox7 the answer in texbox11 will be 10,005. or I will go back by shift tab and then it changes to 15.00. here is my new code...I think it is more efficient based on Greg's input. Your help is much needed

Code:
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox6.Value <> "" Then
        Dowhatyoudo
    End If
    
    TextBox6.Value = Format(TextBox6.Value, "#,##0.00")
End Sub

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox7.Value <> "" Then
        Dowhatyoudo
    End If

    TextBox7.Value = Format(TextBox7.Value, "#,##0.00")
           
End Sub


Private Sub Dowhatyoudo()
        TextBox11.Value = Format(Val(TextBox6.Value) + Val(TextBox7.Value) + Val(TextBox8.Value) + Val(TextBox9.Value) _
        + Val(TextBox10.Value), "$#,##0.00")
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
So you now have a comma?

Val("5,000") = 5
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
OK...what a goober I am, thanks so very much. It totally works.

If I wanted to keep the dollar sign and the common in there, what would I have to do? Is it possible to add them up still? Would I need to convert it to an integer or something? thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top