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 .

    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)

    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.

    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)

    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 ().

    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!

Register Yourself Login