Selected Answer
Divinedar
Quick answer (without time to test)- please check these subs- the seem to make material totals ito INCLUDE the labour cost (see bits in bold):
'====================================CALCULATION OF TEXTBOXES ON USERFORM============================================
'Get Material Cost
Private Sub txtQTY_Change()
'Set textbox for calculation and will re-calculate if changed
If txtMTLcst.Value = "" Then txtMTLcst.Value = 0
If txtLBRcst.Value = "" Then txtLBRcst.Value = 0
If txtQTY.Value = "" Then txtQTY.Value = 0
txtMTLtlt.Value = ((CDbl(txtMTLcst.Value) * CDbl(txtQTY.Value)) + CDbl(txtLBRcst.Value))
txtMTLtlt.Value = Format(WorksheetFunction.RoundUp(txtMTLtlt.Value, 2), "###.00")
End Sub
Private Sub txtMTLcst_Change()
'Set textbox for calculation and will re-calculate if changed
If txtMTLcst.Value = "" Then txtMTLcst.Value = 0
If txtLBRcst.Value = "" Then txtLBRcst.Value = 0
If txtQTY.Value = "" Then txtQTY.Value = 0
txtMTLtlt.Value = ((CDbl(txtMTLcst.Value) * CDbl(txtQTY.Value)) + CDbl(txtLBRcst.Value))
txtMTLtlt.Value = Format(WorksheetFunction.RoundUp(txtMTLtlt.Value, 2), "###.00")
End Sub
Private Sub txtLBRcst_Change()
'Set textbox for calculation and will re-calculate if changed
If txtMTLcst.Value = "" Then txtMTLcst.Value = 0
If txtLBRcst.Value = "" Then txtLBRcst.Value = 0
If txtQTY.Value = "" Then txtQTY.Value = 0
txtMTLtlt.Value = ((CDbl(txtMTLcst.Value) * CDbl(txtQTY.Value)) + CDbl(txtLBRcst.Value))
txtMTLtlt.Value = Format(WorksheetFunction.RoundUp(txtMTLtlt.Value, 2), "###.00")
End Sub
Can that be right?
REVISION 17 March 2023 (In response to user's revised file and discussion point below):
You said 'This is where I get the error
txtDiscAmt.Value = (CDbl(txtTOTALcst.Value) * CDbl(txtDiscPer.Value.Value)) / 100
and it arises from the .Value.Value bit. However the calculation is wrong- the discount should be calculated as a prescentage of your cost PLUS your negotiaton markup values. I've corrected that in the attached revised file as follows (first bold line):
'Get Discount Amount Total
Private Sub txtDiscPer_Change()
'Set textbox for calculation and will re-calculate if changed
If txtTOTALcst.Value = "" Then txtTOTALcst.Value = 0
' make this a default value
If txtDiscPer.Value = "" Then txtDiscPer.Value = 5
' ### error line with wrong calculation
'txtDiscAmt.Value = (CDbl(txtTOTALcst.Value) * CDbl(txtDiscPer.Value.Value)) / 100
txtDiscAmt.Value = (CDbl(txtTOTALcst.Value) + CDbl(txtNegAmt.Value)) * CDbl(txtDiscPer.Value) / 100
'format that to 2 decimal places (change decimal marker if needed)
txtDiscAmt.Value = Format(WorksheetFunction.RoundUp(txtDiscAmt.Value, 2), "#,##0.00")
End Sub
and in the second line I've corrected the formatting so it rounds up first then forces two decimal places (so you see 0.00 and 5.40 rather than .00 and 5.4 for example). I've done that in other procedures too.
That macro causes the Discount amount to change and I've changed that sub as follows:
Private Sub txtDiscAmt_Change()
'Set textbox for calculation and will re-calculate if changed
If txtTOTALcst.Value = "" Then txtTOTALcst.Value = 0
' make this a default value
If txtNegAmt.Value = "" Then txtNegAmt.Value = 0
If txtDiscAmt.Value = "" Then txtDiscAmt.Value = 0
txtSellAmt.Value = (CDbl(txtTOTALcst.Value) + CDbl(txtNegAmt.Value)) - CDbl(txtDiscAmt.Value)
' format that to 2 decimal places (change decimal marker if needed)
'### CORRECTED
txtSellAmt.Value = Format(WorksheetFunction.RoundUp(txtSellAmt.Value, 2), "#,##0.00")
'check price is good
Call SellCheck
End Sub
where the lines in bold call a new sub:
Private Sub SellCheck()
' check if a loss is made (make background red)
If CDbl(txtSellAmt.Value) < CDbl(txtTOTALcst.Value) Then
txtSellAmt.BackColor = 8421631
Else
txtSellAmt.BackColor = -2147483643
End If
End Sub
This causes the selling price textbox to turn red if you give too much discount. E.g. if your total cost is ASD 100.00 say, a 10% markup would give a gross price of ASD 110 but a 10% discount would knock off ASD 11 giving a nett price of ASD 99 (so less than cost).I've done similar for the markup code too.
Note that I've commented out your code for
'Private Sub txtSellAmt_Change()
' ### Commented out this code for the result value (which otherwise changes and retriggers itself)
for the reason above. In fact, a single change in value on your userform already causes multiple macros to be triggered (but it's too fast for you to notice, you just see live updates).
Hope this fixes things for you.
