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]

OK, but what text box number is each of those boxes?

 

[Abdo]

about QTY strat from textbox12 :22 and the PRICE strat from textbox23 :33 and the TOTAL starts from strat from textbox34 :44
as to the green textbox is textbox45 and the expenses is textbox46 .

 

[Joe4]

Maybe something like this:

Private Sub TextBox12_change()

    Dim tot As Double
    Dim exp As Double
    
    If TextBox12 <> "" And TextBox23 <> "" 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)
        End If
'       Populate total
        TextBox34.Value = tot * (1 + exp)
    End If
                
End Sub

However, note that all this is dependent upon when those other text boxes are being calculated.
The order of when things are being updated and when they are being calculated is very important.
This would work much better being directly on the worksheet instead of being done in a User Form, in which none of the calculations are automatic and you have to program each one.

 

[Abdo]

However, note that all this is dependent upon when those other text boxes are being calculated.

I knew it this is big challenge to do that

This would work much better being directly on the worksheet instead of being done in a User Form,

unfortunately , I can't this invoice should copy from form to sheet

BTW there is no error and nothing changes in textbox34 !!!

 

[Joe4]

BTW there is no error and nothing changes in textbox34 !!!

Note that it appears that you have placed this code in the "TextBox12_change" event, which means that this code will ONLY fire when the value in TextBox12 gets updated.
You will probably need to put similar code as this in EVERY TextBox for price and quanity (as you can see, doing it this way is very inefficient and going to get very messy very quickly!).

 

[Abdo]

as you can see, doing it this way is very inefficient and going to get very messy very quickly!).

so what you suggest ? can do that inside the sheet after copy data from userform ?

 

[Joe4]

so what you suggest ? can do that inside the sheet after copy data from userform ?

I already gave my suggestion - not to use UserForms in this manner. If you do, you will need to have VBA code on the EVERY single text box on your form, so that it does the proper calculations.
If you insist on using Excel forms, this is what you will need to do.
It would be much easier to do on the Excel spreadsheet, where you can set up dynamic formulas that calculate automatically.

If this absolutely has to be a form, if you have the ability to use Microsoft Access, the forms there work much better, as you can set up calculated fields.
This is what I would opt to do if I absolutely needed forms.
However, the learning curve of Microsoft Access is a bit steep, so if you are not proficient in Access, it would probably take some time to learn it well enough to set up a proper database and create the forms.

 

[Abdo]

It would be much easier to do on the Excel spreadsheet, where you can set up dynamic formulas that calculate automatically.

how do that ? you need structure data inside sheet?

 

[Joe4]

how do that ? you need structure data inside sheet?

Set up a pre-defined template with a table, very similar to the layout you have on your form, and set up the formulas where needed.
You can even "lock" certain cells down to ensure that users only enter values in the cells that you want them to, and not other cells.

 

[Abdo]

see if it's help

1 (2).xlsm
	A	B	C	D	E
1	ITEM	BRAND	QTY	PRICE	BALANCE
2	1	FD-FOOS1	200	10	2000
3	2	FD-FOOS2	10	10	100
4	3	FD-FOOS3	200	20	4000
5
6
7
8
9	SUB TOTAL				6100
10	EXPENSES				2000
11	TOTAL				6100
s

Cell Formulas
Range	Formula
E11	E11	=SUM(E2:E4)

result

1 (2).xlsm
	A	B	C	D	E
1	ITEM	BRAND	QTY	PRICE	BALANCE
2	1	FD-FOOS1	200	10	2655.738
3	2	FD-FOOS2	10	10	132.786
4	3	FD-FOOS3	200	20	5311.476
5
6
7
8
9	SUB TOTAL				6100
10	EXPENSES				2000
11	TOTAL				8100
s

Cell Formulas
Range	Formula
E11	E11	=SUM(E2:E4)