UserForm Calculations in Currency.

[Hardhat]

I have 6 TextBoxes that I am trying to calculate Currency in. I have the math working fine what I cannot figure out is how to get the last to TextBoxes to show as currency. The user will only be entering information into the first 3 TextBoxes. We are comparing costs example of the three equations are "A * C= D", "B * C= E"," D - E=F " I have the first 2 TextBoxes(A&B) change to currency AfterChange event. TextBox "C" is a quantity so no need for it to show a currency. TextBox "D" is a total of the first equation and have that showing as currency on change event. Textboxes "E & F" I can not show as currency. I have exhausted my limited Knowledge on this. Thank you in advance for any help. Below is the could I currently am using.

Private Sub TBCCPu1_AfterUpdate()
TBCCPu1.Value = Format(TBCCPu1.Value, "currency")
End Sub

Private Sub TBCCPu1_Change()
 If IsNumeric(Me.TBCCPu1.Value) = True Or Me.TBCCPu1.Value = vbNullString Then
        'Good data, nothing to MSG
    Else
        MsgBox "Your input data is not valid"
        TBCCPu1 = ""
        Exit Sub
    End If
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
End Sub

Private Sub TBNCPu1_AfterUpdate()
 TBNCPu1.Value = Format(TBNCPu1.Value, "currency")
End Sub

Private Sub TBNCPu1_Change()
 If IsNumeric(Me.TBNCPu1.Value) = True Or Me.TBNCPu1.Value = vbNullString Then
        'Good data, nothing to MSG
    Else
        MsgBox "Your input data is not valid"
        TBNCPu1 = ""
        Exit Sub
    End If
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
End Sub

Private Sub TBCoU1_Change()
If IsNumeric(Me.TBCoU1.Value) = True Or Me.TBCoU1.Value = vbNullString Then
        'Good data, nothing to MSG
    Else
        MsgBox "Your input data is not valid"
        TBCoU1 = ""
        Exit Sub
    End If
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
End Sub

Private Sub TBCACoG1_Change()
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
TBCACoG1.Value = Format(TBCACoG1.Value, "currency")
End Sub

Private Sub TBNACoG1_Change()
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value

Private Sub TBImpact1_Change()
If TBCCPu1.Value = "" Then TBCCPu1.Value = 0
If TBCoU1.Value = "" Then TBCoU1.Value = 0
If TBCACoG1.Value = "" Then TBCACoG1.Value = 0
If TBNCPu1.Value = "" Then TBNCPu1.Value = 0
If TBNACoG1.Value = "" Then TBNACoG1.Value = 0
If TBImpact1.Value = "" Then TBImpact1.Value = 0
TBCACoG1.Value = TBCCPu1.Value * TBCoU1.Value
TBNACoG1.Value = TBNCPu1.Value * TBCoU1.Value
TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value

 

[dmt32]

Hi,
give following a try & see if will do what you want

Make a backup of your workbook & delete ALL your existing textbox codes

Add ALL codes below to your userforms code page

Private Sub TBCCPu1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = CancelEntry(Me.TBCCPu1)
End Sub

Private Sub TBCoU1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = CancelEntry(Me.TBCoU1)
End Sub

Private Sub TBNCPu1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = CancelEntry(Me.TBNCPu1)
End Sub

Function CancelEntry(ByVal TextBox As Object) As Boolean
    With TextBox
        If IsNumeric(.Value) Then
            If Val(.Value) > 0 Then
                TBCACoG1.Value = Format(Val(TBCCPu1.Value) * Val(TBCoU1.Value), "currency")
                TBNACoG1.Value = Format(Val(TBNCPu1.Value) * Val(TBCoU1.Value), "currency")
                TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
            End If
        Else
            If Len(.Text) > 0 Then
                MsgBox "Your input data is not valid", 48, "Invalid Entry"
                CancelEntry = True
            End If
            .Value = 0
        End If
    End With
End Function

Private Sub UserForm_Initialize()
'*****DO NOT RENAME THIS EVENT TO MATCH YOUR USERFORM NAME
'*********************************************************
'user entry fields
    TBCCPu1.Value = 0
    TBCoU1.Value = 0
    TBNCPu1.Value = 0
    
'calculated fields
    With TBCACoG1
        .Value = Format(0, "currency")
        .Locked = True
    End With
    With TBNACoG1
        .Value = Format(0, "currency")
        .Locked = True
    End With
    With TBImpact1
        .Value = 0
        .Locked = True
    End With
End Sub

Hopefully, I have interpreted your project correctly but you should adjust as required

Hope Helpful

Dave

 

[Hardhat]

dmt32,

Thank you, You did interpret exactly what I was looking for. I did at a few tweaks to your code to get the Impact Textbox to show a currency also. I hope I can pay you help forward in the future as I continue to learn more VBA.

Function CancelEntry(ByVal TextBox As Object) As Boolean
    With TextBox
        If IsNumeric(.Value) Then
            If Val(.Value) > 0 Then
                TBCACoG1.Value = Format(Val(TBCCPu1.Value) * Val(TBCoU1.Value), "currency")
                TBNACoG1.Value = Format(Val(TBNCPu1.Value) * Val(TBCoU1.Value), "currency")
                TBImpact1.Value = TBCACoG1.Value - TBNACoG1.Value
            'add this line So the TBImpact shows as currency'
                TBImpact1.Value = Format(TBImpact1.Value, "currency")
            End If
        Else
            If Len(.Text) > 0 Then
                MsgBox "Your input data is not valid", 48, "Invalid Entry"
                CancelEntry = True
            End If
            .Value = 0
        End If
    End With
End Function

With TBImpact1
        .Value = Format(0, "currency")'changed value from 0'
        .Locked = True

 

[dmt32]

dmt32,

Thank you, You did interpret exactly what I was looking for. I did at a few tweaks to your code to get the Impact Textbox to show a currency also. I hope I can pay you help forward in the future as I continue to learn more VBA.

Hi,
Glad updated suggestion does what you want.
This is a voluntary forum & all contributors give their time for free - just post a question when in need of assistance with code you have problems with & as much explanation as possible - plenty here to assist you

Many thanks for feedback

Dave