How can I sum value in 8 textboxes to the a single textbox?

Question

I can't find a way to do this. I think it's a very basic question, but i'm just new with excel VBA and can't find the right formula.

Recently i understand that to do the sum, i have to change each of the textboxes into integer first, then i can add it to a single textbox, so i'm converting each value into integer while i sum them one by one to a text box.

I have tried this code.

Private Sub txtTotal_Change() 
txtTotal.Value = CInt(txtKas) + CInt(txtInvestasi) + CInt(txtDanaTerbatas) + CInt(txtBruto) + ...
End Sub

How can i sum this multiple textboxes (8 textboxes) into a single textbox?

Solution 1

You need to add Change Events on your text boxes that are user changeable.

Lets say I have below UserForm and TextBox1 to TextBox3:

TextBox3 will be the Sum of TextBox1 and TextBox2.

Right click UserForm1 under Project and select View Code, then put in the TextBoxesSum Sub (I use Double type for accepting decimals):

Private Sub TextBoxesSum()
    Dim Total As Double
    Total = 0
    If Len(TextBox1.Value) > 0 Then Total = Total + CDbl(TextBox1.Value)
    If Len(TextBox2.Value) > 0 Then Total = Total + CDbl(TextBox2.Value)
    ' Add more for the rest of your text boxes
    TextBox3.Value = Total
End Sub

To be safe and smart, you should also put in key checking from user input. Due to the amount of text boxes you have it is better to have a Function to handle it:

Private Function NumericOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Dim Key As MSForms.ReturnInteger
    Select Case KeyAscii
        Case 46, 48 To 57 ' Accept only decimal "." and numbers [0-9]
            Set Key = KeyAscii
        Case Else
            KeyAscii = 0 ' Minor bug earlier
            Set Key = KeyAscii
    End Select
    Set NumericOnly = Key
End Function

Now back to the UserForm1 object, right click TextBox1 and View Code, put in:

Private Sub TextBox1_Change()
    TextBoxesSum
End Sub

Also do checking on the KeyPress event:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = NumericOnly(KeyAscii)
End Sub

Now copy above 2 set of TextBox Sub and paste below, then replace the string before underscore to match the name of the text boxes you have.

Sample output:

Solution 2

You (probably) do not want to sum the values from your textboxes when someone tries to change the value in the totals box - you want to sum them when a value in any of the other eight boxes change.

The simplest way to do this is probably to create a simple macro to sum the values of the textboxes, and then call that from the change-events of the 8 which the user may change.

The macro to sum the textboxes and put them into the totals box

Private Sub sum_boxes()
    txtTotal = CLng(txtKas.Value) + CLng(Investasi.Value) + CLng(DanaTerbatas.Value)
End Sub

What a change event will look like

Private Sub txtKas_Change() 
    Call sum_boxes
End Sub

You need to make one change-event for each of the eight boxes, as mentioned previously.

On a completely different note, using a textbox to store the total may be a bad idea, as you don't want your users to change what's in it. One option is to lock the textbox, as PatricK suggests, another is to use a different object to hold the number, e.g. a label, and just change its caption to be similar to whatever your total is at the moment.

Comments

[user3292755]

Thank you @eirikdaude I have followed your code, but it's says object require for this code txtTotal = CLng(txtKas.Value) + CLng(Investasi.Value) + CLng(DanaTerbatas.Value)

[user3292755]

Thanks for the reply @PatricK finally i got the answer for this problem. I really appreciate your help

[user3292755]

How if the user make mistake? are there the built in function for Undo button in excel VBA?

[PatricK]

Undo on user made mistake? AFAIK, there isn't a built in feature to Undo. You will have to implement it from scratch. It will be complicated code, are you up for that? You should instruct users to be careful/mindful when entering data (we have stopped them putting anything other than numbers and decimal - but not multiple decimals).

[user3292755]

Maybe i should study more about VB, i thought that there's an Undo button for this. Thank you for your advise Patrick.