Excel VBA event handling

This type of code will help you deal with negatives as a result of formulas. As it is setup, it looks at any of the cells in Column B (Column (2) in the code. Modify to suit.

I will warn that this code fires whenever the sheet calculates, which happens with nearly every change. That said, it can cause your file to run more slowly as the file size increases. Let me know if it helps.

Please Login or Register  to view this content.

Thanks Norie, workbook_sheetchange is a step closer to what I want. So you mentioned using sheet_calculate, do I have to user it on every sheet then?

Thanks bigbas that's kind of exactly what I wanted. Do I put that code on every worksheet? When I put it on worksheet10 and change values in worksheet1 making values in worksheet10 go negative, it throws me some kind of method intersect of global failed. It does work if I'm actually on worksheet10 though. Also it's giving me a msgbox everytime an yvalue changes whether is negative or positive, but I think I can probably figure how to fix the code so it only does it for negative values only.

Based on my understanding (and anyone else can chime in if I'm incorrect), the Workbook Scope SheetCalculate code may not be appropriate if there are linkages among sheets. I believe that Excel runs the code for each sheet subsequently; if multiple sheets link to an original sheet, the code will run for each dependent sheet. For example, if Sheets 2, 3 and 4 each have a formula that links to sheet 1, then if this value becomes negative, you will get 3 separate message boxes (1 per sheet).

I believe the BEST approach is to write code in a module, and have that code called whenever there is a workbook change. This is a two step process. First, enter the below code in a STANDARD MODULE. Note: as per before, the code will check column 2 for negative values, as defined by the Const col As Long = 2
line of code

Please Login or Register  to view this content.

Next, enter the following code in the ThisWorkbook module:

Please Login or Register  to view this content.

Report back if this helps.

Thanks it works now!!