textbox calculation

[northernstar]

morning all

i have a form which has a few text boxes on it and i have wrote the code to find the average of the 4 boxes (added them together then divided by 4, used .controls, val and .text

now i would like to find the varition between the 4 for example if the 4 values are

1, 1.005, 1.003 and 1.002

the variation is 1.005-1.000=0.005

basically the max-min but vba doesnt appear to recognise max and min unless i am doing something wrong?

also would like to set the format of the boxes used

textbox1= format(textbox1,"0.0000") which works fine if i use a BeforeUpdate event but this relies on moving to another box which is ok until you reach the last box?

i would appreciate if anyone could point me in the right direction

thanks in advance

[Simon Lloyd]

Take a look at http://www.vbaexpress.com/forum/showthread.php?t=12710, it will have a lot of information to set you on the right track!

[unmarkedhelicopter]

you can write code that runs every time a change occurs in the textbox but imagine if you wanted to enter 23 you would enter 2 then the coe would kick in and format the cell as 2.0000 then where is your cursor ? for when you type the 3 ?

A better way is to load the boxes with "0.0000" when you start and then format them as you leave (as you are doing). It gives the user a clue as to what is expected, You can also have the OK (or whatever) run the format's for you anyway, so they are all updated before any subsequent use.

[Bob Phillips]

Use Application.MAX(Val(TextBox1.Text), Val(TextBox2.Text),...

and Application.Min