VBA: Summing userform textboxes formatted as currency

[RusselJ]

Hi all

Textbox 1 and 2 are formatted as currency.

How can these textboxes be summed in the Total textbox? I tried using

Total.Value = Format(Textbox1.Value + Textbox2.Value, "Currency")

This is the remaining code:

Private Sub Textbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Textbox1 = "" Then
Exit Sub
End If
If IsNumeric(Textbox1) Then
Textbox1 = Format(Textbox1.Value, "Currency")
Total.Value = Format(Textbox1.Value + Textbox2.Value, "Currency")
Else
MsgBox "Please enter a numerical value"
Cancel = True
End If
End Sub

Private Sub Textbox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Textbox2 = "" Then
Exit Sub
End If
If IsNumeric(Textbox2) Then
Textbox2 = Format(Textbox2.Value, "Currency")
Total.Value = Format(Textbox1.Value + Textbox2.Value, "Currency")
Else
MsgBox "Please enter a numerical value"
End If
End Sub

Would appreciate your thoughts?

Russel

 

[Aaron Blood]

If you assign the textbox values to a variable dimensioned as a Double, the Currency formatting should not be an issue...

    TextBox1.Value = Format(TextBox1.Value, "Currency")
    TextBox2.Value = Format(TextBox2.Value, "Currency")

    
    Dim x As Double, y As Double
    x = TextBox1.Value
    y = TextBox2.Value

    
    TextBox3.Value = Format(x + y, "Currency")

 

[RusselJ]

Hi Aaron

Thanks for your suggestions, that works great. What about if one of the text boxes was blank, it doesn't like that! Probably because it is trying to add something that isn't there.

What do you think is the best way of dealing with that? I will have 9 text boxes so some will be blank,

Thanks again for your help

Russel

 

[Aaron Blood]

Any number of approaches for an Error Handler that would assign zero instead of the TextBox value would be fine...

Or something as simple as skipping over the variable assignment in the event an error is encountered (who knows, someone enters a space or letter?) would probably be fine as well. Something like this maybe:

    TextBox1.Value = Format(TextBox1.Value, "Currency")
    TextBox2.Value = Format(TextBox2.Value, "Currency")

    
    Dim x As Double, y As Double
    On Error Resume Next
        x = TextBox1.Value
        y = TextBox2.Value
    On Error GoTo 0

    
    TextBox3.Value = Format(x + y, "Currency")

 

[harveya915]

I kind of have the same issue. I have a UserForm with a total of 7 TextBoxes. However when typing in something to TextBoxes 3 & 4, it will be in currency, which means I will type in a "$" sign (ex. $1,234.56). I need for those two to be summed up in Box5 and for the result to also show a "$" sign. I don't know where I'm supposed to put in these lines of code. I have put them into my UserForm code but there seems to be a error whenever I try and type the "$" sign. If I don't include the $ sign it works great (but it only shows 1.5 instead of 1.50) This is what I have on my UserForm code:

Private Sub UserForm1_Initialize()

'Empty TextBox1
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""


With UseForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show

End With

End Sub

Do I put the code somewhere in here, or does it go on "This Workbook" or "Sheet1"? I have other CommandButtons on this UserForm with code as well but I don't think they're relevant to this therefore didn't include the whole code for the UserForm.
FYI, I am a complete noob. Don't know much about coding but i do my best googling. Any help would be appreciated.