vba 如何将 8 个文本框中的值与单个文本框相加？

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

提问by user3292755

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.

我找不到办法做到这一点。我认为这是一个非常基本的问题，但我刚接触 excel VBA，找不到正确的公式。

Recently i understand that to do the sum, i have to change each of the textboxesinto 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.

最近我了解到要进行求和，我必须textboxes先将textbox每个值更改为整数，然后才能将其添加到单个值中，因此我将每个值转换为整数，同时将它们一一求和到文本框。

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?

我如何将这多个文本框（8 个文本框）汇总到一个文本框中？

采纳答案by PatricK

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

您需要在用户可更改的文本框中添加更改事件。

Lets say I have below UserForm and TextBox1to TextBox3:

TextBox3 will be the Sum of TextBox1 and TextBox2.

假设我有以下 UserForm 和TextBox1到TextBox3：

TextBox3 将是 TextBox1 和 TextBox2 的总和。

Right click UserForm1under Project and select View Code, then put in the TextBoxesSumSub (I use Double type for accepting decimals):

右键单击Project 下的UserForm1并选择View Code，然后放入TextBoxesSumSub （我使用 Double 类型接受小数）：

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:

现在回到 UserForm1 对象，右键单击 TextBox1 和View Code，输入：

Private Sub TextBox1_Change()
    TextBoxesSum
End Sub

Also do checking on the KeyPress event:

还要检查 KeyPress 事件：

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.

现在复制上面 2 组 TextBox Sub 并粘贴到下面，然后替换下划线之前的字符串以匹配您拥有的文本框的名称。

Sample output:

示例输出：

回答by eirikdaude

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.

最简单的方法可能是创建一个简单的宏来对文本框的值求和，然后从用户可能更改的 8 的更改事件中调用它。

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.

另一方面，使用文本框来存储总数可能不是一个好主意，因为您不希望用户更改其中的内容。一种选择是锁定文本框，正如 PatricK 建议的那样，另一种选择是使用不同的对象来保存数字，例如标签，然后将其标题更改为与您目前的总数相似。