run time error 94 , invalid use of null

i'm getting run time error 94 , invalid use of null
how to solve this? thx

Condition:
  • 6 unbound textboxes , all set to Null onload.
  • if only 1 textbox retreive value input by user , txtTotal show that value and ignore other null textboxes.(User may input to txt2 or others as first value and leave txt1 as null)
  • if 2 textboxes retreive values, txtTotal shows sum of those 2 values and ignore other null textboxes.
  • if more textboxes retreive value,condition same as above
  • txtTotal is updated when any of the textboxes retreive value.


Codes :
Private Sub txt1_AfterUpdate()
Me.txtTotal = Val(txt1.Value) + Val(txt2.Value) + Val(txt3.Value) + Val(txt4.Value) + Val(txt5.Value)
End Sub

same code goes to Private Sub txt2_AfterUpdate() and so on.
Poop HolyAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Try adding this function in a standard module.
Function SumAll(ParamArray vals()) As Single
Dim I As Long

    For I = LBound(vals) To UBound(vals)
        SumAll = SumAll + Val(Nz(vals(I), 0))
    Next I
    
End Function

Open in new window

You can then use it in the AfterUpdate event of the textboxes like this.
Private Sub Text1_AfterUpdate()
    Me.txtTotal = SumAll(txt1.Value, txt2.Value, txt3.Value, txt4.Value, txt5.Value)
End Sub

Open in new window

0
 
John TsioumprisSoftware & Systems EngineerCommented:
Me.txtTotal = Val(Nz(txt1.Value,"") + Val(Nz(txt2.Value,"") + Val(Nz(txt3.Value,"") + Val(Nz(txt4.Value,"") + Val(Nz(txt5.Value,"")

Open in new window

0
 
Poop HolyAuthor Commented:
Solved my problem perfectly, thx bro
0
 
aikimarkCommented:
Private Sub txt1_AfterUpdate()
    Me.txtTotal = Val(""&txt1.Value) + Val(""&txt2.Value) + Val(""&txt3.Value) + Val(""&txt4.Value) + Val(""&txt5.Value)
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.