problem with my code for summing several textboxes together and placing sum in another textbox...

[kbishop94]

The code allows for any value > than 0, and use of 1 decimal. The problem is it does not like when the decimal is the first character entered. I get error: "Run time error '13': Type mismatch"

What do I need to change so that it allows a decimal to be entered as the first character?

I have this code for 6 textboxes total: (the 5 others are identical except for the text box name.)

Private Sub txtCostProd_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case 46
            If InStr(1, txtCostProd, ".") > 0 Then KeyAscii = 0
        Case 48 To 57
        Case Else
            KeyAscii = 0
    End Select
End Sub

And this one for executing the sum:

(note: 'txtCost1' is just for a label that is positioned over top of the 'txtCost' textbox in order to prevent the user from entering anything into txtCost field... I didnt like the method of disabling the text box 'txtCost' becuase I didn't like how light the font became when the field was disabled. Doing it this was I was I could control the color/shade of the font that appears and still not allow anything to be entered into the field.)

Private Sub TextBoxesSum()
    Dim Total As Double
    Total = 0
    
    If Len(txtCostProd.Value) > 0 Then Total = Total + CDbl(txtCostProd.Value)
    If Len(txtCostShip.Value) > 0 Then Total = Total + CDbl(txtCostShip.Value)
    If Len(txtCostConcess.Value) > 0 Then Total = Total + CDbl(txtCostConcess.Value)
    If Len(txtCostTravel.Value) > 0 Then Total = Total + CDbl(txtCostTravel.Value)
    If Len(txtCostFacility.Value) > 0 Then Total = Total + CDbl(txtCostFacility.Value)
    If Len(txtCostOther.Value) > 0 Then Total = Total + CDbl(txtCostOther.Value)


    txtCost.Value = Total
    txtCost1.Value = Total
End Sub

 

[dmt32]

Hi,
try this change to your code

Private Sub TextBoxesSum()
    Dim Total As Double
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If Len(Ctrl.Name) > 7 And Ctrl.Name Like "txtCost*" Then Total = Total + Val(Ctrl.Value)
    Next Ctrl
    
    With Me.txtCost
        .Value = Total
        .Locked = True
    End With


End Sub

note: 'txtCost1' is just for a label that is positioned over top of the 'txtCost' textbox in order to prevent the user from entering anything into txtCost field.

To prevent users entering data in a textbox, you can use the controls locked property (shown in RED). Setting this value to True, you cannot edit the controls value.

Just a comment, if you are repeating all the keypress events for each control as well as change events to call the TexBoxesSum code then may want to consider creating a class for your controls that call these two codes.


Hope Helpful

Dave