Summing Text Boxes and Keeping Proper Formatting

[reberryjr]

I have 5 text boxes that I want to total up in a 6th text box. The first 5 are formatted as such, upon initialization of the form.

Me.txt_DPPaid = Format(0, "$#,##0.00")

When I test the form, the default value of 0 is presented as I want it to be, as in $0.00. Now, I've googled a bunch, and watched a couple of YouTube videos on how to sum. Here's what seems to be working as far as summing, but I can't seem to be able to keep the formatting in all 6 text boxes.

Private Sub txt_DPPaid_Change()'Me.txt_DPPaid = Format(Me.txt_DPPaid, "$#,##0.00") - this is only taking the first number I type in, and then only progressing by 1 cent
'Me.txt_DPPaid = CCur(Me.txt_DPPaid) - this actually removes the formatting.
Call SumTotalPaid
End Sub
Private Sub SumTotalPaid()
On Error Resume Next
'Me.txt_TotalPaid = (Me.txt_DPPaid + 0) + (Me.txt_DCPaid + 0) + (Me.txt_OCPaid + 0) + (Me.txt_CTIPaid + 0) + (Me.txt_CTOPaid + 0)
Me.txt_TotalPaid = (IIf(Me.txt_DPPaid = 0, 0, Me.txt_DPPaid) + (IIf(Me.txt_DCPaid = 0, 0, Me.txt_DCPaid) + (IIf(Me.txt_OCPaid = 0, 0, Me.txt_OCPaid) _
    + (IIf(Me.txt_CTIPaid = 0, 0, Me.txt_CTIPaid) + (IIf(Me.txt_CTOPaid = 0, 0, Me.txt_CTOPaid))))))
Me.txt_TotalPaid = Format(Me.txt_TotalPaid, "$#,##0.00")
End Sub

Any suggestions?

 

[Rick Rothstein]

Unlike a cell, TextBoxes do not have a Format property, they simply display the text that is assigned to them. The format you assigned during initialization only applied to the number in the Format function that you used... the format does not persist upon entering a new value. So when you assign a value to a TextBox, you must do so using the Format function if you want to have that value display in a certain way.

 

[reberryjr]

Rick, so I'm confused on how to keep the formatting that I've set at initialization, as data is entered in the text box. I'm still trying to teach myself VBA and I've read that Excel considers text boxes just that...text. Do I need to change the data type in the text boxes to ensure that the formatting is kept? I added this line of code in the text box change event code, but it's still only letting me capture the first digit that I enter, and moving to the end of the field. Meaning if I enter $250.00, $0.00 is being changed to $2.00, and the cursor is moving to the end of the second decimal.

Private Sub txt_DPPaid_Change()'Me.txt_DPPaid = Format(Me.txt_DPPaid, "$#,##0.00") - this is only taking the first number I type in, and then only progressing by 1 cent
'Me.txt_DPPaid = CCur(Me.txt_DPPaid) - this actually removes the formatting.
Call SumTotalPaid
Me.txt_DPPaid = Format(Me.txt_DPPaid, "$#,##0.00")
End Sub

 

[Fluff]

How about using the Exit event

Private Sub txt_DPPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_DPPaid.Value = Format(txt_DPPaid.Value, "$#,##0.00")
End Sub

 

[mikerickson]

Introducing the "$" makes summing difficult. I'd recommend putting the "$" symbol in a label next to the Textbox and leaving it out of the text box.

Then code like this will keep the text box formatted the way you want. )You'd have to set the .Tag property of each text box once in the Initialize event, and have an _AfterUpdate and _Change event for each of the formatted text boxes.)

Private Sub UserForm_Initialize()
    TextBox1.Tag = "#,##0.00"
End Sub

Private Sub TextBox1_AfterUpdate()
    With TextBox1
        .Text = Format(Val(.Text), .Tag)
    End With
End Sub

Private Sub TextBox1_Change()
    If RealActiveControl.Name <> TextBox1.Name Then
        Call TextBox1_AfterUpdate
    End If
End Sub

Property Get RealActiveControl() As MSForms.Control
    Dim Container As Object
    Set Container = Me
    
    Do
        Set RealActiveControl = Container.ActiveControl
        Select Case TypeName(RealActiveControl)
            Case "Frame", "Page"
                Set Container = RealActiveControl
                Set RealActiveControl = Nothing
            Case "MultiPage"
                With RealActiveControl
                    Set Container = .Pages(.Value)
                End With
                Set RealActiveControl = Nothing
        End Select
    Loop While RealActiveControl Is Nothing
End Property

And the summing code becomes

Private Sub SumTotalPaid()
    Me.txt_TotalPaid = Val(Me.txt_DPPaid) + Val(Me.txt_DCPaid) + Val(Me.txt_OCPaid) + Val(Me.txt_CTIPaid) + Val(Me.txt_CTOPaid)
End Sub

 

[reberryjr]

@Fluff, that worked! I have to ask, can you recommend a site that will help teach me these things? I'm thinking that I might need to slow down with my efforts, until I get a handle on more of the basics.

 

[Fluff]

This is an excellent site to learn from, most of what I know has been from here.

You can also have a look at the list Hiker95 has put together
https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions.html

 

[reberryjr]

@Fluff, I lied. I only tested it on the DP Paid text box. When I extrapolated the code its "sister" fields, the code in the Total Paid text box only seems to be handling 4 of the 5 text boxes that feed it. Meaning, if I enter $5 in 4 fields the Total Paid text box says $20. When I enter $5 in the 5th text box (doesn't matter which one), the Total Paid text box reverts to showing $5.00$5.00$5.00$5.00$5.00. In reviewing the code below, I'm not seeing the issue.

Private Sub txt_CTOPaid_Change()Call SumTotalPaid
End Sub
Private Sub txt_DPPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_DPPaid.Value = Format(txt_DPPaid.Value, "$#,##0.00")
End Sub
Private Sub txt_DCPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_DCPaid.Value = Format(txt_DCPaid.Value, "$#,##0.00")
End Sub
Private Sub txt_OCPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_OCPaid.Value = Format(txt_OCPaid.Value, "$#,##0.00")
End Sub
Private Sub txt_CTIPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_CTIPaid.Value = Format(txt_CTIPaid.Value, "$#,##0.00")
End Sub
Private Sub txt_CTOPaid_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_CTOPaid.Value = Format(txt_CTOPaid.Value, "$#,##0.00")
End Sub
Private Sub SumTotalPaid()


On Error Resume Next


Me.txt_TotalPaid = (IIf(Me.txt_DPPaid = 0, 0, Me.txt_DPPaid) + (IIf(Me.txt_DCPaid = 0, 0, Me.txt_DCPaid) + (IIf(Me.txt_OCPaid = 0, 0, Me.txt_OCPaid) _
    + (IIf(Me.txt_CTIPaid = 0, 0, Me.txt_CTIPaid) + (IIf(Me.txt_CTOPaid = 0, 0, Me.txt_CTOPaid))))))


Me.txt_TotalPaid = Format(Me.txt_TotalPaid, "$#,##0.00")


End Sub

 

[Fluff]

Have you had a look at mikericson post#5

 

[reberryjr]

I didn't see it until you mentioned it. Reviewing.