Sum textboxes on a userform in a textbox

R

Racquet

Board Regular
Joined
Feb 3, 2006
Messages
80
I have 3 text boxes on a user form, titled txt1, txt2, txt3. The first two textboxes are to be used for entry of a number. The third text box needs to automatically reflect the sum of the first two text boxes whenever an entry is made in either text box or the original entry is updated. I have code to prevent entry of a non numeric entry (and a message to tell the user that the entry must be numeric). However, I cannot get the third box to reflect a total . The result that I am obtaining now is an entry of 4 in txt1 and 5 in txt 3 displays as $4$5 in txt3. Hopefully there is a very simple solution to this question. The code is as follows:

VBA Code: 
Private Sub txt1_Change()
 
    Txt1.BackColor = vbWhite
    If Not IsNumeric(Txt1.Value) Then
        Txt1.Value = ""
        Txt1.BackColor = vbYellow
        MsgBox ("This entry must be a positive whole number!")
        Me.Txt1.Value = 0
    Else
        Me.Txt1.Value = Format(Me.Txt1.Value, "$#,##0")
    End If
    Txt3.Value = Format((Txt1.Value) + (Txt2.Value), "$#,##0")
     
End Sub

Private Sub txt2_Change()

Txt2.BackColor = vbWhite
   If Not IsNumeric(Txt2.Value) Then
        Txt2.Value = ""
        Txt2.BackColor = vbYellow
        MsgBox ("This entry must be a positive whole number!")
        Me.Txt2.Value = 0
    Else
        Me.Txt2.Value = Format(Me.Txt2.Value, "$#,##0")
    End If
      Txt3.Value = Format((Txt1.Value) + (Txt2.Value), "$#,##0")

End Sub
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hi,
try following & see if will do what you want

Place following codes in your forms code page

VBA Code: 
Private Sub Txt1_Change()
    Txt3.Value = Format((Val(Txt1.Value)) + (Val(Txt2.Value)), "$#,##0")
End Sub
Private Sub Txt2_Change()
    Txt3.Value = Format((Val(Txt1.Value)) + (Val(Txt2.Value)), "$#,##0")
End Sub

Private Sub Txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub
Private Sub Txt2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub

Place this code in a standard module if you want to use it with other userforms in your project.

VBA Code: 
Function WholeNumbersOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    'dmt32 Jan 2021
    If Chr(KeyAscii) Like "*[!0-9]*" Then KeyAscii = 0
    Set WholeNumbersOnly = KeyAscii
End Function

Above function limits users to keying in whole number values only.

Hope Helpful

Dave
 
Upvote 0
dmt32 said:
Hi,
try following & see if will do what you want

Place following codes in your forms code page

VBA Code: 
Private Sub Txt1_Change()
    Txt3.Value = Format((Val(Txt1.Value)) + (Val(Txt2.Value)), "$#,##0")
End Sub
Private Sub Txt2_Change()
    Txt3.Value = Format((Val(Txt1.Value)) + (Val(Txt2.Value)), "$#,##0")
End Sub

Private Sub Txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub
Private Sub Txt2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub

Place this code in a standard module if you want to use it with other userforms in your project.

VBA Code: 
Function WholeNumbersOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    'dmt32 Jan 2021
    If Chr(KeyAscii) Like "*[!0-9]*" Then KeyAscii = 0
    Set WholeNumbersOnly = KeyAscii
End Function

Above function limits users to keying in whole number values only.

Hope Helpful

Dave
Click to expand...
Unfortunately, the code that you suggested does not work. I do not get a sum in txt3 as I had hoped. Instead, I just get a 0. Also, I am noticing that an attempt to enter $4.23 in txt1 and $5.23 in txt2 results in an entry of $423 in $523 in txt2.

I added the Function code that you suggested into a standard module, and added
Code: 
Private Sub Txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub
Private Sub Txt2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     KeyAscii = WholeNumbersOnly(KeyAscii)
End Sub

into the UserForm. Doesn't seem to help.

Thank you for any more suggestions that you might have.
 
Last edited by a moderator:
Upvote 0
If the values are preceded with a $ the Val function will convert the value(s) in the textbox(es) to 0.

Also, the function WholeNumbersOnly restricts, as it's name suggests, what's entered to whole numbers, i.e. you can't have decimal places.

If don't format what's entered with a $ and don't use the function then the sum should work.
 
Upvote 0
Hi,
@Norie has kindly answered reasons your issues
Solution is based on your post which in your code this "This entry must be a positive whole number" is included.
I took this to mean that you are only entering whole numbers in txt1 and txt2 not decimal values which function as written, will not allow.
Also, a little puzzled by your claim to have entered $ sign in txt1 and txt2 as again, function will not allow it so you must be using other codes.

I can modify function to include allowing decimal values if that what you need but firstly I would suggest that you clear all codes in your form for txt1 & txt2 & re-install my codes as directed to confirm that they are working OK for you with whole numbers.

Dave.
 
Upvote 0
Hello Racquet,
maybe this codes are little bit closer to your needs.
VBA Code: 
Option Explicit

Dim varTxt1, varTxt2
Dim varTxt3 As Double


Private Sub txt1_Change()
    
    If InStr(1, Txt1.Value, ".") Then
        varTxt1 = Format(Txt1.Value, "0.00")
    End If
    If Txt1.Value = "" Then
        varTxt1 = 0
        Txt3.Value = ""
        GoTo ex
    End If
    If Not IsNumeric(Txt1.Value) Then
        varTxt1 = Left(Txt1.Value, Len(Txt1.Value) - 1)
        Txt1.Value = varTxt1
    Else
        varTxt1 = CDbl(Txt1.Value)
ex:     If varTxt2 = "" Then varTxt2 = 0
        Txt3.Text = varTxt1 + varTxt2
    End If
    If Txt1.Value = "" And Txt2.Value = "" Then GoTo ex2
    Txt3.Value = Format(Txt3.Text, "$#.##0")
    Exit Sub
ex2:
    Txt3.Value = ""
    
End Sub


Private Sub txt2_Change()
    
    If InStr(1, Txt2.Value, ".") Then
        varTxt2 = Format(Txt2.Value, "0.00")
    End If
    If Txt2.Value = "" Then
        varTxt2 = 0
        Txt3.Value = ""
        GoTo ex
    End If
    If Not IsNumeric(Txt2.Value) Then
        varTxt2 = Left(Txt2.Value, Len(Txt2.Value) - 1)
        Txt2.Value = varTxt2
    Else
        varTxt2 = CDbl(Txt2.Value)
ex:     If varTxt1 = "" Then varTxt1 = 0
        Txt3.Text = varTxt1 + varTxt2
    End If
    If Txt1.Value = "" And Txt2.Value = "" Then GoTo ex2
    varTxt2 = Txt2.Value
    Txt3.Value = Format(Txt3.Text, "$#.##0")
    Exit Sub
ex2:
    Txt3.Value = ""

End Sub
 
Upvote 0
EXCEL MAX said:
Hello Racquet,
maybe this codes are little bit closer to your needs.
VBA Code: 
Option Explicit

Dim varTxt1, varTxt2
Dim varTxt3 As Double


Private Sub txt1_Change()
  
    If InStr(1, Txt1.Value, ".") Then
        varTxt1 = Format(Txt1.Value, "0.00")
    End If
    If Txt1.Value = "" Then
        varTxt1 = 0
        Txt3.Value = ""
        GoTo ex
    End If
    If Not IsNumeric(Txt1.Value) Then
        varTxt1 = Left(Txt1.Value, Len(Txt1.Value) - 1)
        Txt1.Value = varTxt1
    Else
        varTxt1 = CDbl(Txt1.Value)
ex:     If varTxt2 = "" Then varTxt2 = 0
        Txt3.Text = varTxt1 + varTxt2
    End If
    If Txt1.Value = "" And Txt2.Value = "" Then GoTo ex2
    Txt3.Value = Format(Txt3.Text, "$#.##0")
    Exit Sub
ex2:
    Txt3.Value = ""
  
End Sub


Private Sub txt2_Change()
  
    If InStr(1, Txt2.Value, ".") Then
        varTxt2 = Format(Txt2.Value, "0.00")
    End If
    If Txt2.Value = "" Then
        varTxt2 = 0
        Txt3.Value = ""
        GoTo ex
    End If
    If Not IsNumeric(Txt2.Value) Then
        varTxt2 = Left(Txt2.Value, Len(Txt2.Value) - 1)
        Txt2.Value = varTxt2
    Else
        varTxt2 = CDbl(Txt2.Value)
ex:     If varTxt1 = "" Then varTxt1 = 0
        Txt3.Text = varTxt1 + varTxt2
    End If
    If Txt1.Value = "" And Txt2.Value = "" Then GoTo ex2
    varTxt2 = Txt2.Value
    Txt3.Value = Format(Txt3.Text, "$#.##0")
    Exit Sub
ex2:
    Txt3.Value = ""

End Sub
Click to expand...

My needs changed a bit based on the suggestions that were made and as well as what I was able to get set up. I do have a lot to learn about vba. That said, I believe code that works is "good code" and simpler is better. I wanted a simple application where the user could not type in something like "I don't know", hence the need for numeric information only. It turns out that the code that I came up will only accept whole numbers. I also wanted a userform that automatically computes a total. I think I have that now. This is the code that I came up.

VBA Code: 
Private Sub Txt1_Change()
    Txt1.BackColor = vbWhite
    If Not IsNumeric(Txt1.Value) Then
        Txt1.Value = ""
        Txt1.BackColor = vbYellow
        MsgBox ("This entry must be a positive whole number!")
        Me.Txt1.Value = 0
    Else
        Me.Txt1.Value = Format(Me.Txt1.Value, "$#,##0")
    End If
   
    RecalculateTotal
    
End Sub

Private Sub txt2_Change()
    Txt1.BackColor = vbWhite
    If Not IsNumeric(txt2.Value) Then
        txt2.Value = ""
        txt2.BackColor = vbYellow
        MsgBox ("This entry must be a positive whole number!")
        Me.txt2.Value = 0
    Else
        Me.txt2.Value = Format(Me.txt2.Value, "$#,##0")
    End If
   
    RecalculateTotal
           
End Sub

Public Sub RecalculateTotal()
    Dim Tot As Double
    On Error Resume Next
    Tot = Tot + CDbl(Txt1.Text)
    Tot = Tot + CDbl(txt2.Text)
    Txt3.Text = Format((Tot), "$#,##00")
End Sub

Thanks again for reading my post and making some suggestions.
 
Last edited by a moderator:
Upvote 0
Racquet said:
My needs changed a bit based on the suggestions that were made and as well as what I was able to get set up. I do have a lot to learn about vba. That said, I believe code that works is "good code" and simpler is better. I wanted a simple application where the user could not type in something like "I don't know", hence the need for numeric information only. It turns out that the code that I came up will only accept whole numbers. I also wanted a userform that automatically computes a total. I think I have that now. This is the code that I came up.

VBA Code:
Private Sub Txt1_Change()
Txt1.BackColor = vbWhite
If Not IsNumeric(Txt1.Value) Then
Txt1.Value = ""
Txt1.BackColor = vbYellow
MsgBox ("This entry must be a positive whole number!")
Me.Txt1.Value = 0
Else
Me.Txt1.Value = Format(Me.Txt1.Value, "$#,##0")
End If

RecalculateTotal

End Sub

Private Sub txt2_Change()
Txt1.BackColor = vbWhite
If Not IsNumeric(txt2.Value) Then
txt2.Value = ""
txt2.BackColor = vbYellow
MsgBox ("This entry must be a positive whole number!")
Me.txt2.Value = 0
Else
Me.txt2.Value = Format(Me.txt2.Value, "$#,##0")
End If

RecalculateTotal

End Sub

Public Sub RecalculateTotal()
Dim Tot As Double
On Error Resume Next
Tot = Tot + CDbl(Txt1.Text)
Tot = Tot + CDbl(txt2.Text)
Txt3.Text = Format((Tot), "$#,##00")
End Sub
VBA Code:

Thanks again for reading my post and making some suggestions.
Click to expand...
Well, it isn't perfect, I haven't figures out how to fix it.

If I type the number 1 in the first text box and type 0 in the second text box, the third box shows 01. If I type 10 in the first text box and in the second text box, the third box shows 10. If type 10 in the first two text boxes, the third box shows 20 (which is what I want).

Does anyone have a suggestion as to how I fix this code?
 
Upvote 0
Your format code is wrong. You should use:

Code: 
 Txt3.Text = Format((Tot), "$#,##0")

rather than:

Code: 
 Txt3.Text = Format((Tot), "$#,##00")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Sense checking UserFrom code
Replies
0
Views
289
AndyEd
A
K
  • Solved
VBA Spin buttons in user form - how to declare them as a variable?
Replies
2
Views
2K
Kra
K
R
  • Solved
Summing textboxes on userform
2 3
Replies
20
Views
785
Akuini
Akuini
P
  • Solved
Userform textbox format when populated by listbox
Replies
9
Views
405
dmt32
D
K
  • Question
Achieve specific behavior in a textbox (Crosspost)
2
Replies
10
Views
574
Micron
M

Forum statistics

Threads
1,215,461
Messages
6,124,957
Members
449,200
Latest member
indiansth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top