validated, stop all calculations on bad data

[cate]

I need some help on validation.

User input value X is restricted by other user input values found in
other cells. There are so many of these, with so many rules, I've
decided to do it in code.

There must be a begin calculations event. I'll jump in there and
validate. If bad data is found I will notify the user and stop wb
processing. But how to stop? If calculations can be aborted, how do
I turn it "on" again? The user still needs to enter data and I still
need to check it.

Just looking for ideas before I try and implement something and have
to do it all over again later.

Thank you.

 

[Dave Peterson]

I think you'll find that you can't get stop the calculation nicely once it starts.

But you can tie into different events, like the worksheet event:
Private Sub Worksheet_Calculate()
or the workbook event:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

There are other events that fire when the user makes a change by typing (not
formulas re-evaluating).

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/events.htm

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

There are application events, too.
You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

===============
You may want to consider using helper cells that return error messages (in
adjacent cells???).

You could have formulas that check this range of warning/error messages and
return more warnings instead of doing the actual calculation that you need.

For instance, if the original formula is as simple as: =sum(sheet1!a:a)

you could use:
=if(countif(sheet1!b:b,"Error")>0,"You have errors",sum(sheet1!a:a))

You could do the same kind of thing in code to stop any processing you want:

if application.countif(worksheets("Sheet1").range("b:b"),"error") > 0 then
'your warning
exit sub '???
end if