Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validated, stop all calculations on bad data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validated, stop all calculations on bad data
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 he 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 On 05/03/2011 07:34, cate wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a list from validated data | Excel Discussion (Misc queries) | |||
How to stop Automatic calculations? | Excel Discussion (Misc queries) | |||
Using 'OR' with validated data | Excel Worksheet Functions | |||
How do I stop calculations? | Excel Discussion (Misc queries) | |||
Mortgage amortize, stop calculations at end of term | Excel Programming |