Equation Miscaculation

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Hi there
When I put this formating to the value of TextBoxs,

Code:
Gtot.Value = Format(Gtot.Value, "#,##0.000")
tot.Value = Format(tot.Value, "#,##0.000")
bal.Value = Format(bal.Value, "#,##0.000")
However, when i cancel the formating then the equation gives correct result but with no comma nor period.....which are realy needed

Then the result of this line is always 1 or not correct
Code:
bal.Value = val(Gtot.Value) - val(tot.Value)

How can get the correct result out of equation ?

The whole code:

Code:
Private Sub cmdAdd_Click()

MyCalc
tot.Value = ""

End Sub

Private Sub bal_Change()
bal.Value = Format(bal.Value, "#,##0")
End Sub

Private Sub tot_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'tot.Value = Format(tot.Value, "#,##0.000")
    With Me.ActiveControl
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            MsgBox "Sorry, only numbers allowed"
            .Value = vbNullString
        ElseIf Left(.Value, 1) = "-" And IsNumeric(Left(.Value, 2)) Then
            Exit Sub
        End If
    End With
End Sub

Private Sub Gtot_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Gtot.Value = Format(Gtot.Value, "#,##0.000")
    With Me.ActiveControl
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            MsgBox "Sorry, only numbers allowed"
            .Value = vbNullString
        ElseIf Left(.Value, 1) = "-" And IsNumeric(Left(.Value, 2)) Then
            Exit Sub
        End If
    End With
End Sub

Sub MyCalc()
If tot.Value = "" Then Exit Sub

If tot.Value <> 0 Then
bal.Value = val(Gtot.Value) - val(tot.Value)
    Exit Sub
End If

End Sub
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
This seems to work correctly :-
Code:
Private Sub CommandButton1_Click()
    gtot.Value = Format(gtot.Value, "#,##0.000")
    tot.Value = Format(tot.Value, "#,##0.000")
    '---------------------------------------------
    bal.Value = CDbl(gtot.Value) - CDbl(tot.Value)
    bal.Value = Format(bal.Value, "#,##0.000")
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,909
Messages
5,574,986
Members
412,630
Latest member
Eireangel
Top