won't let me format with textbox

[rlink_23]

On this userform I have been working on, basically I have TextBox1 + TextBox2 = Subtotal, Then subtotal + TextBox3 = Grand Total

Which the code looks like this

Private Sub TextBoxSum()    Dim Total As Double
    Total = 0
    If Len(TextBox1.Value) > 0 Then Total = Total + Val(TextBox1Value)
    If Len(TextBox2.Value) > 0 Then Total = Total + Val(TextBox2.Value)
    
    
    Subtotal.Value = Total
    
    
End Sub

Then I have

Private Function NumericOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger   Dim Key As MSForms.ReturnInteger
   Select Case KeyAscii
        Case 46, 48 To 57
               Set Key = KeyAscii
        Case Else
             KeyAscii = 0
                Set Key = KeyAscii
        End Select
        Set NumericOnly = Key
End Function

Then each corresponding TextBox_ keypress is this

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)KeyAscii = NumericOnly(KeyAscii)
End Sub

Then lastly i have the change event for eatch textbox as

Private Sub TextBox1_Change()TextBoxSum
End Sub

This seems like a lot of typing for such a small task but All I am trying to do, and I cannot figure it out for the lkife of me is I want to format TextBox1 like this
textBox1.Value = Format(TextBox1.Value, "$#,##0.00")

Which is fine it formats to that but it quits summing the textboxes once added. I want it to format AND sum the texboxes but I am failing at this simple task and thought I would ask for help beofre I rip my hair out lol... Any thoughts would be greatly appreciated Thank you so kindly

 

[dmt32]

All I am trying to do, and I cannot figure it out for the lkife of me is I want to format TextBox1 like this
textBox1.Value = Format(TextBox1.Value, "$#,##0.00")

Which is fine it formats to that but it quits summing the textboxes once added.

Hi,
your procedure uses the Val function to coerce the TextBox strings to a numeric value - Val function will stop reading the string once it encounters the first non-numeric character in your case $ which may explain your problem.

Try either running the procedure Before you apply the format to your textboxes or modify your procedure to bypass the $ character.

Private Sub TextBoxSum()
    Dim Total As Double
    Total = 0
    If Len(TextBox1.Value) > 0 Then Total = Total + Val(Mid(Me.TextBox1.Text, 2))
    If Len(TextBox2.Value) > 0 Then Total = Total + Val(Mid(Me.TextBox2.Text, 2))
    
    
    Subtotal.Value = Total
    
End Sub

and see if any of these ideas solve your problem

Dave

 

[rlink_23]

Try either running the procedure Before you apply the format to your textboxes or modify your procedure to bypass the $ character.

Private Sub TextBoxSum()
    Dim Total As Double
    Total = 0
    If Len(TextBox1.Value) > 0 Then Total = Total + Val(Mid(Me.TextBox1.Text, 2))
    If Len(TextBox2.Value) > 0 Then Total = Total + Val(Mid(Me.TextBox2.Text, 2))
    
    
    Subtotal.Value = Total
    
End Sub

and see if any of these ideas solve your problem

Dave[/QUOTE]


So I tried this, I had to change the procedure a little bit because it was bypassing hte first number in the dollar amount. So if it was 150 + 150 it would equal 100. So I changed it to this

Private Sub TextBoxSum()    Dim Total As Double
    Total = 0
    If Len(textBox1.Value) > 0 Then Total = Total + [COLOR=#ff0000]Val(Mid(Me.textbox1.Text, 1))[/COLOR]
    If Len(textbox2.Value) > 0 Then Total = Total + [COLOR=#ff0000]Val(Mid(Me.textbox2.Text, 1))


[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000"]#000[/URL] 000][/COLOR]
    
    
    Subtotal.Value = Total
    
    
End Sub

with that little change it adds correctly BUT,

Adding the formatting to the change event on TextBox1 and TextBox2, and I tried it both and before and after the TextBoxSum, I now get "False" in TextBox1 and TextBox2...

Private Sub engineprice_Change()

textbox1.Value = textbox1.Value = Format(textbox.Value, "$#,##0.00")
TextBoxSum


End Sub

 

[rlink_23]

disregard the this portion... Idk where it came from lol

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000"]#000[/URL] 000][/COLOR]

 

[dmt32]

try chaning this line

textbox1.Value = textbox1.Value = Format(textbox.Value, "$#,##0.00")

to this

TextBox1.Value = Format(TextBox1.Value, "$#,##0.00")

Also, you need to apply Val function after the $ in your textboxes otherwise it will return 0.

Dave

 

[rlink_23]

Still no dice..... Again it formats the pricing but does not add it with the other TextBoxes And not sure where you are saying to apply the val function after the dollar sign...

 

[dmt32]

Still no dice..... Again it formats the pricing but does not add it with the other TextBoxes And not sure where you are saying to apply the val function after the dollar sign...

Hi,
your procedure TextBoxSum worked ok for me with adjustment I suggested.

What you have in the the TextBoxes are strings & if you format the textboxes with a currency symbol, Val function will see that symbol & stop reading any further along the string - as $ sign is the 1st character in the string Val returns 0.

I suggested you combine the Val function with the Mid function to begin reading the Textbox string from the second character

Val(Mid(Me.TextBox1.Text, 2))

as I said, this did work for me.


Hope helpful

Dave

 

[rlink_23]

I tried it both ways and still doesn't work. When I do this bit of code

[COLOR=#333333]Val([/COLOR][COLOR=#ff0000]Mid(Me.TextBox1.Text, 2)[/COLOR][COLOR=#333333])[/COLOR]

I have to change the 2 to a 1 because when you have a number 115.00 + 115.00 = 30.00 as it skips the first number in the line. I changed it to a 1 and it is back to not summing the textboxes.

 

[dmt32]

I tried it both ways and still doesn't work. When I do this bit of code

[COLOR=#333333]Val([/COLOR][COLOR=#ff0000]Mid(Me.TextBox1.Text, 2)[/COLOR][COLOR=#333333])[/COLOR]

I have to change the 2 to a 1 because when you have a number 115.00 + 115.00 = 30.00 as it skips the first number in the line. I changed it to a 1 and it is back to not summing the textboxes.

OK lets try another update to your textboxsum procedure

Private Sub TextBoxSum()
    Dim Total As Double
    Total = 0
    With Me.TextBox1
        If Len(.Value) > 0 Then Total = Total + IIf(Left(.Value, 1) = "$", Val(Mid(.Text, 2)), Val(.Text))
    End With
    
    With Me.TextBox2
        If Len(.Value) > 0 Then Total = Total + IIf(Left(.Value, 1) = "$", Val(Mid(.Text, 2)), Val(.Text))
    End With
    
    Subtotal.Value = Format(Total, "$#,##0.00")
    
End Sub

adjustment should sum textbox with or without $ symbol.

Dave

 

[rlink_23]

So we are now closer... For example I have $1,341.00 + $450.00 and it formats right and the TextBoxes now add but The Total is 451.00, So I assuming that it is adding the 1 in 1341.00 plus the 450.00 which does not make sense to me as you think it would be doing it in TextBox2 as well... Ugh