textbox calculation

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

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!

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.

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

and Application.Min