Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum values in multiple textboxes on userfrom without button

0

Hi

I try to  sum  the  whole  values  for  multiple textboxes  in another  textbox  on userform  without depend  on  the  commandbutton . I  try  to  put  the  code  in change event into specific textbox  but  doesn't sum  any thing .what  I  want  when  add  values for  any textbox ,then  should  show summing  values in textbox45  

Private Sub TextBox35_Change()
For y = 35 To 44
If Me.Controls("TextBox" & y) = vbNullString Then
Me.Controls("TextBox" & y) = ""
End If
Next y
On Error Resume Next
x = CDbl(TextBox35.Text) + CDbl(TextBox36.Text) + CDbl(TextBox37.Value) + CDbl(TextBox38.Value) + CDbl(TextBox39.Value) + CDbl(TextBox40.Value) + CDbl(TextBox41.Value) + CDbl(TextBox42.Value) + CDbl(TextBox43.Value) + CDbl(TextBox44.Value)
TextBox45.Text = x
TextBox45.Text = Format(TextBox45.Text, "#,###.00")
End Sub

and every textbox contains like  this

Private Sub TextBox35_AfterUpdate()
TextBox35.Text = Format(TextBox35.Text, "#,###.00")

End Sub

thanks in advance.

excel
MS Office Version: 2019
posted by: Hasson (rep: 30) on Thu Aug 11, 2022 at 5:28 am
Answer
Discuss

Answers

0
Selected Answer

Hasson

Your problem is that the file

x = CDbl(TextBox35.Text) + CDbl(TextBox36.Text) + CDbl(TextBox37.Value) + CDbl(TextBox38.Value) + CDbl(TextBox39.Value) + CDbl(TextBox40.Value) + CDbl(TextBox41.Value) + CDbl(TextBox42.Value) + CDbl(TextBox43.Value) + CDbl(TextBox44.Value)

fails if ANY textbox 35-44 has no number in it.

in the attached file, I've written a small sub which loops through the textboxes and adds them to the total (if they contain a number). I've added comments to help:

Private Sub UpdateTotal()
'reset total
x = 0
'loop through controls
For y = 35 To 44
    If Not Me.Controls("TextBox" & y) = vbNullString Then
        ' add to total if TB value <>""
        x = x + CDbl(Me.Controls("TextBox" & y).Text)
    End If
Next y

On Error Resume Next
' write Total
TextBox45.Text = Format(x, "#,###.00")

End Sub

Then each TB Change sub just needs to read:

Private Sub TextBox35_Change()
    Call UpdateTotal
End Sub
Private Sub TextBox36_Change()
    Call UpdateTotal
End Sub
Private Sub TextBox37_Change()
    Call UpdateTotal
End Sub

I have only done that for 35-37 so you need to do the same for 38 to 44.Then the form should work fully.

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Thu Aug 11, 2022 at 7:13 am
Discuss

Discussion

Hi John,
  this  is  not  my  file you  uploaded . I  copy  the  codes  and  put  in  my  file 
works  perfectly .
many  thanks for your  answering . 
Hasson (rep: 30) Aug 11, '22 at 7:52 am
Thanks for selecting my Answer, Hasson.

Sorry about the wrong file- it's now corrected in the Answer.
John_Ru (rep: 6142) Aug 11, '22 at 8:04 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login