Excel Get Value From TextBox

Question

I have a few textboxes for me to get value from it. I have sample code for it. But it not auto change every time I update textbox. How to auto update?

Dim ader, ader1, ader2, ader3, ader4 As Double
Sub TextBox1_BeforeUpdate()
    On Error Resume Next
    Dim tb, tb1, tb2, tb3 As TextBox
    Set tb = ActiveSheet.TextBoxes("TextBox 51")
    Set tb1 = ActiveSheet.TextBoxes("TextBox 46")
    Set tb2 = ActiveSheet.TextBoxes("TextBox 48")
    Set tb3 = ActiveSheet.TextBoxes("TextBox 45")
    ader = CDbl(tb.Text) + CDbl(tb1.Text) + CDbl(tb2.Text)
    ader1 = CDbl(tb.Text)
    ader2 = CDbl(tb1.Text)
    ader3 = CDbl(tb2.Text)
    ader4 = CDbl(tb3.Text)
    Application.CalculateFull
End Sub
Public Function valu() As Double
    Application.Volatile
    valu = ader
End Function
Public Function valu1() As Double
    Application.Volatile
    valu1 = ader1
End Function
Public Function valu2() As Double
    Application.Volatile
    valu2 = ader2
End Function
Public Function valu3() As Double
    Application.Volatile
    valu3 = ader3
End Function
Public Function valu4() As Double
    Application.Volatile
    valu4 = ader4
End Function

Answer 1

I suggest that you forget about VBA. Instead, set the LinkedCell property of each of your text boxes to the address of a cell covered by the Tbx. This has the effect that whatever you type in the Tbx is instantly transferred to the sheet but remains invisible because the cell is covered by the Tbx.

Presuming that your textboxes are linked to cells D3, D5 and D7, you can now write a formula as shown below to display the entered total.

=SUM(D3, D5, D7)

Of course, you can achieve the same result with VBA but this method appears to be easier to both implement and maintain. The LinkedCell property takes a simple string like D3.