help regarding excel macro!!

[Farooko]

I have made a simple SPC program for the company I work. I am not a computer expert but I manage to make a statistical process control charts. All it does that you have 26 columns where you have to put 5 values in each column. Then it calculates the mean, range and standard deviation. There are many different sheets in a workbook basically one for each different manufacturing process. Each sheet has different control limits. I have used conditional formatting so that when ever the value is outside the control limit it turns red. And if the average of 5 values is outside the limit operator has to mention the error code which are from 1 to 9 (which they are not doing it). I want to develop something like that when ever the average of the five values is outside the control limit the operator cannot enter further data unless they put an action code first. I have two ideas how to do it but can’t make a macro as I am not very good in programming.

i) First of all I thought that when ever the average value turns red (out of control) this triggers the macro and they can’t do anything except for entering the fault code first. And once they have entered the fault code value they can carry on as normal. There is one problem with this that the average is going to turn red suppose the first value is out of limit. But I really want to trigger the macro once we have put all the 5 values in.

ii) Secondly, I was thinking to put the control limits for each chart on specific sheet and the macro takes the values from those cells and assign a variable say x and y to them. And so we can use if then else loop and say that if the values are within these limits everything is fine and if the values are outside x and y, the user cannot enter any data unless he put an action code first.

I hope I have explained my self as clearly as possible. I am really stuck and its very urgent for me. Hope to hear from some one really soon.

[msanchez]

VBA can be a little tedious, but with as much as you've done thus far, I'm sure you can pick up the basics rather quickly. This site I found through Google, the author has compiled myriad functions, macros and formulas in Excel, as well as how to use the Macro recorder and the VB editor in Excel.

The rare times I have to write some VBA, I typically use the Macro recorder to record the basics of what I need done, then I open that macro in the VB editor and code the rest of my custom logic. This way, you will see the core of your VBA function written. The editor has Intellisense for functions and properties available to you for a given object.

http://www.excel-vba.com/

Good luck,