Can I do addition, division and multiplication in the same textbox on the userform

[Abdo]

Hello
is there any way to do addition, division and multiplication in the same textbox on the userform
this the part of my code and I will implement for multiple textboxes

Private Sub TextBox12_change()
If TextBox12 <> "" And TextBox23 <> "" Then
  TextBox34.Value = CDbl(TextBox45.Value) / CDbl(TextBox44.Value)
    TextBox34.Value = CDbl(TextBox12) * CDbl(TextBox23) *CDbl(TextBox34.Value)
  End If
End Sub

I have textbox 44 it will sum values multiple textboxes and textbox 45 I have to fill value manually , then the result should be in textbox34 for instance
textbox 44= 87700, textbox45=2000 so the value = 2000/87700=0.022805
textbox 12=10 textbox23=100 result 10*100=1000 then the result in textbox34= 1000*0.022805=22.805
I no know if it's possible or not . any idea to do that ?

 

[Joe4]

see if it's help

If it helps what?

Yes, something like that is how I would do it.
So, do you still have some unresolved questions/issues?

 

[Abdo]

actually I don't want Decimal number ,then this is not totally matched with the value in EXPENSES how treat this?

 

[Joe4]

actually I don't want Decimal number ,then this is not totally matched with the value in EXPENSES how treat this?

Then you will need to use the ROUND formula, rounding off to zero decimals.
Just realize that removing precision like that can have adverse affects in certain cases (i.e. the total value not adding up to the actual value).

For example, if you had 10 divided by 3, you would have:
3.333
3.333
3.333

If you round each of those off, you have:
3
3
3
which totals 9, not 10!

That is the danger in removing precision - your rounded off valeus may not SUM up exactly to your original value.

One way around that is to adjust the first or last value in your list, to be the value of TOTAL EXPENSES minus the sum of all the other rounded values.
In our example above, it would then return:
3
3
4

 

[Abdo]

One way around that is to adjust the first or last value in your list, to be the value of TOTAL EXPENSES minus the sum of all the other rounded values.
In our example above, it would then return:
3
3
4

I thought there is function to deal with this . this is not practical if the values EXPENSES will change every time I have to add some values every time to equal the TOTAl EXPENSES .

 

[Joe4]

I thought there is function to deal with this . this is not practical if the values EXPENSES will change every time I have to add some values every time to equal the TOTAl EXPENSES .

These are the direct consequences of your decision to ignore precision and round it off. Anytime you round values used in a total, that is the risk you take, that the total of the smaller rounded values will not added up to the original amount.

Note that I am not recommending doing anything manually, I am recommending accommodating it in your formula.
Instead of using the normal calculation for the first entry, take the grand total and subtract the total from all the other individual lines to get "what is left over", and it will always add up.

 

[Abdo]

Hi Joe
I came back to your code and after many tries I reached to solution but not completely
sometimes gives error values . I think the problem beacuase of this

TextBox46.Value = Format(TextBox46.Value, "#,###.00")

so here is example pic1 and result in pic2 is right but pict3 is wrong !!

here is the code

Private Sub TextBox46_AfterUpdate()
Dim tot As Double
    Dim exp As Double
    
    If TextBox12 <> "" And TextBox23 <> "" And TextBox34 <> "" And TextBox46.Value <> "" Then
'       Calculate quantity * price
        tot = CDbl(TextBox12.Value) * CDbl(TextBox23.Value)
'       Check to see if a value in Expense box
        If TextBox46 <> "" Then
'           Calculate expense ratio
            exp = CDbl(TextBox46.Value) / CDbl(TextBox45.Value)
            ElseIf TextBox46 = "" Then Call UpdateTotal
        End If
'       Populate total
        TextBox34.Value = tot * (1 + exp)
    End If
  TextBox46.Value = Format(TextBox46.Value, "#,###.00")
End Sub

 

[Joe4]

I came back to your code and after many tries I reached to solution but not completely
sometimes gives error values . I think the problem beacuase of this

TextBox46.Value = Format(TextBox46.Value, "#,###.00")

so here is example pic1 and result in pic2 is right but pict3 is wrong !!

I thought we moved off of the Form model and were working on a worksheet model instead...
I really have no other thoughts to offer on the Form model. There is so much interaction going on with all the other text boxes and VBA code, I cannot really make a good suggestion when only considering a single text box/VBA code. It all has to be done in relation to all the other ones.
That is a pretty big undertaking, not one I am willing to undertake (and probably falls more into a "consultant" situation).