Textboxes, chage event and math

[Hlatigo]

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.

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

 

[Greg Truby]

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]

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]

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.

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]

Are you actually entering the $ signs?

If you are then Val might be returning 0 values.

 

[Hlatigo]

I am not entering any $ signs but the formatting is...should I take it out?

 

[Norie]

Yes.

 

[Hlatigo]

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

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]

So you now have a comma?

Val("5,000") = 5

 

[Hlatigo]

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