Error When Formatting TextBox Value To Then Perform Another Action

[rameezl17]

Hi Everyone,

I have A UserForm With 3 TextBoxes - TextBox1, TextBox2, TextBox3 (To Make This Example Simple)

I have the following codes in TextBox1:

Private Sub TextBox1_Change()
Me.TextBox1.Text = Format(Me.TextBox1.Text, "#,###,###,###")
TextBox3.Value = Val(TextBox1) * Val(TextBox2)
End Sub



TextBox2 Has the same code As TextBox1:

Private Sub TextBox2_Change()
Me.TextBox2.Text = Format(Me.TextBox2.Text, "#,###,###,###")
TextBox3.Value = Val(TextBox1) * Val(TextBox2)
End Sub

Now I want TextBox3 To Display The Total Of TextBox1 * TextBox2. I notice that when I have that code to format the number with the thousands separator, the total that shows in TextBox3 is only the multiplication of each textbox's value BEFORE the thousands separator. Hence, not displaying the proper value. BUT when I take off the formatting code, the multiplication works perfectly. Does anybody know how to fix this or what I am doing wrong? I noticed that this happens whenever I do any form of operation not just multiplication. I need the thousands separator in the TextBoxes to make it easier on the user's eyes, and ensure that they do not mistype a value.

For an Example: If TextBox1 = 10 And TextBox2 = 5,000. TextBox3 shows 50, opposed to 50,000.

Thank you for your help!

 

[dmt32]

Hi,
Val type conversion function only recognises the period (.) or full stop as valid decimal separtor. Anything else, the function will not read past which may explain results you are getting.

You should be able to get around this by using CDbl function however, it is not as forgiving as Val function when invalid data is entered & will error so will need some additional code to manage.

Private Sub TextBox1_Change()
    On Error Resume Next
    Me.TextBox1.Value = Format(Me.TextBox1.Text, "#,###,###,###")
    TextBox3.Value = CDbl(TextBox1) * CDbl(TextBox2)
    On Error GoTo 0
End Sub




Private Sub TextBox2_Change()
    On Error Resume Next
    Me.TextBox2.Text = Format(Me.TextBox2.Text, "#,###,###,###")
    TextBox3.Value = CDbl(TextBox1) * CDbl(TextBox2)
    On Error GoTo 0
End Sub

Solution untested but hopefully, will now do what you want.

Dave

 

[rameezl17]

Hi Dave,

Thank you so much! I've been dealing with this for a while now and could not find a solution. It works great!

 

[dmt32]

Hi Dave,

Thank you so much! I've been dealing with this for a while now and could not find a solution. It works great!

As a tip - some answers can be found in VBA helpfile

Glad issue resolved.

Dave