 # excel calculation using function formula in userform

• hi there,

i had a request to complete my task , i explain in details . i have a 7 text box and one combobox ,in total result i have to find in textbox 8. but my calculation is quite complicated.

Code:

1. textbox 8= (textbox1+textbox2*48,82 euro+textbox3*40,86 euro+textbox4*47,51 euro+textbox5*67,42 euro +textbox6*47,94 euro+textbox7)*combobox1 value )

this calculation i did in my worksheet it's work fine but if i try to do it in userform it's give me a calcuation is not correct. i attach my useform for more clear understand . i put the manually the value in textbox 1 to textbox 7 then i get the value in textbox 8 .

combobox 1 has a value. something like this : combobox 1: 2,5

the result i need in textbox 8 with euro sign as my image here i attach .

## Images

• The entry in a TextBox is by default a String, so you need to change it to a numeric value, in this case I would use Currency.

Code
``cCur(Me.TextBox1.value)``

Your line of code above is not clear, you cannot use 56 Euros in code or formulas, it must be 56

• roy thanks for quick response but my code i develope here is like this:

Code
``````Private Sub TextBox8_Enter()
Me.TextBox8.Value = Val(TextBox1.Text) + Val(TexBox2.Text) * 48.82 + Val(TexBox3.Text) * 40.86 + Val(Textbox4.Text) * 47.51 + Val(TextBox5.Text) * 67.42 + Val(TextBox6.Text) * 47.94 + Val(TextBox7.Text) * Val(combobox1.Text)
end sub``````

my problem when i put textbox 1 = 4000 it does'nt replace value in

 4.000,00 €

or whatever calculation becomes after i put value in textbox 1 to textbox 7

 and the rest of other calculation i don't need euro sign ,just i need final result in textbox 8= 12.130,00 €

hope i explain it

• You will need to use the exit event of TextBox1. If you use FormatCurrency it will use the default currency for your regional settings

Code
``````Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = FormatCurrency(CCur(Me.TextBox1.Value))
End Sub``````
• oh that's work like a charm but the final result in texbox 8 is still remain problem.sorry but last things need to understand textbox 1 exit event is working fine but my given calculation is need to change something?

Private Sub TextBox8_Enter()

Me.TextBox8.Value = Val(TextBox1.Text) + Val(TexBox2.Text) * 48.82 + Val(TexBox3.Text) * 40.86 + Val(Textbox4.Text) * 47.51 + Val(TextBox5.Text) * 67.42 + Val(TextBox6.Text) * 47.94 + Val(TextBox7.Text) * Val(combobox1.Text)

end sub

• i want to share you a screenshot of my work which i did in excel worksheet and the formula you see in the screen is working well in excel file but i try to build the same idea in my userform which i am not able.

so the same formula in english should me like this =

Code
``textbox 8= ROUND.EXCESS(sum(textbox1+textbox2*48,82+textbox3*40,86+textbox4*47,51 +textbox5*67,42 +textbox6*47,94+textbox7)*combobox1 value ;10)``

## Images

• hi,

here my excel file in attached.

• hi roy, the excel workbook which i have that has many information very sensitive. could you plx give me your e-mail adress if you don't mind.

hope you help me.

• ok i attach here the file as a example .sorry for my inconvenient.

## Files

• I'm not sure what your formula isbut try something like this

Code
``textbox 8= application.worksheetfunction.Round(.EXCESS(sum(textbox1+textbox2*48,82+textbox3*40,86+textbox4*47,51 +textbox5*67,42 +textbox6*47,94+textbox7)*combobox1.value ;10)``
• sorry i try but not working. any idea other way?

• I don't understand your formula.

Maybe

Code
``textbox 8= application.worksheetfunction.Round.EXCESS(sum(textbox1+textbox2*48,82+textbox3*40,86+textbox4*47,51 +textbox5*67,42 +textbox6*47,94+textbox7)*combobox1.value ;10)``

You need to remove sensitive data before attaching a workbook

• yes i remove the sentitive data before to send you . i try same as your last given code write now but not working. and in my file you can see my userform attach where i try to make my calculation.

• i did the formula in my user form

Code
``````Private Sub TextBox8_Enter()
Me.TextBox8.Value =application.worksheetfunction.Round.EXCESS(sum( TextBox1 + TexBox2* 48.82 + TexBox3* 40.86 + Textbox4* 47.51 + TextBox5* 67.42 + TextBox6* 47.94 + TextBox7 )* combobox1)

end sub``````

get error

Edited once, last by royUK ().

•  =CEILING(2.5, 1) Rounds 2.5 up to nearest multiple of 1 3

this is what i want to mean is Roundup

• Are you using Round with Ceiling?

Code
``TextBox 8 = Application.WorksheetFunction.Round(Application.WorksheetFunction.Ceiling(Application.WorksheetFunction.Sum(TextBox1 + TextBox2 * 48, 82 + textbox3 * 40, 86 + textbox4 * 47, 51 + textbox5 * 67, 42 + textbox6 * 47, 94 + textbox7) * combobox1.Value, 10))``
• no only Ceiling , i try with this code and it's working

Private Sub Textbox8_Enter()

Me.Textbox8.Value = Application.WorksheetFunction.Ceiling(Application.WorksheetFunction.Sum(Textcosto + TextUtm * 48, 82 + textUte * 40, 86 + TextboxUtd * 47, 51 + TextMeccanico * 67, 42 + textelectrico * 47, 94 + Textbox8) * Textbox9.Value, 10)

you r great --- but last question

i need to see the result in this way how could i develope?

textbox8 = 12,560 in euro?

• This is with CEILING

TextBox 8 = Application.WorksheetFunction.Ceiling(Application.WorksheetFunction.Sum(TextBox1 + TextBox2 * 48, 82 + textbox3 * 40, 86 + textbox4 * 47, 51 + textbox5 * 67, 42 + textbox6 * 47, 94 + textbox7) * combobox1.Value, 10)

Note you also use SUM

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!