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
Sub DoTheJig()
Dim ws As Worksheet
Dim r As Range
Dim val As Long
Const col As Long = 2
Application.ScreenUpdating = False
val = 0
For Each ws In Worksheets
Set r = Intersect(ws.UsedRange, ws.Columns(col))
If Not r Is Nothing Then
val = val + Application.WorksheetFunction.CountIf(r, "<0")
End If
Next ws
If val >= 1 Then
MsgBox "This change resulted in negative inventory. Please make changes!"
End If
Application.ScreenUpdating = True
End Sub
Next, enter the following code in the ThisWorkbook module:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
DoTheJig
End Sub
Report back if this helps.
Bookmarks