worksheet_calculate triggered wrongly whenever data source is updated

Question

My workbook is essentially for Statistical Process Control

I have multiple worksheets - WorksheetA and WorksheetB , C , D ....

WorksheetA is where users enter / update new data manually

WorksheetB, C and the rest each contains 2 pivot tables - PivotTable1 and PivotTable2. PivotTable1 filters the raw data based on the product group from WorksheetA and displays the in process data dynamically. Data from PivotTable1 is then copied into other columns (say, Column A to C) in the same worksheet and an excel formula evaluates whether is there any out of control situation. If there is, then the value of the data will be displayed, otherwise it will show N/A.

As I would like all out of control situation to be logged down, PivotTable2 captures data in Column A to C and only logs down the data with a value (it means that they are out of control) and ignore those with N/A.

Now, I would like excel to display a textbook that says "You have new out of control situation" whenever PivotTable2 has new value. In Cell D1, I have a total count of the no. of data in PivotTable2. This is where VBA comes in :-

1) When the count in Cell D1 is updated with a new value, the textbox will automatically pop out. Here's the code :-

Private Sub Worksheet_Calculate()
Static countvalue
If Range("D1").Value <> countvalue Then
    countvalue = Range("D1").Value
    MsgBox "You have new out of control situation"
End If
End Sub

2) However, in order for the first portion to work, PivotTable1 and PivotTable2 have to auto-refresh whenever new values are entered (in order for Cell D1 count to be updated). Therefore, in WorksheetA, I have another data count in Cell F1 which records the total number of data in one of the columns (let's call this Column Z). Whenever someone manually enters new data in to Column Z, the count in Cell F1 changes and triggers this auto-refreshing formula :-

Private Sub Worksheet_Calculate()
Static autorefresh
If Range("F1").Value <> autorefresh Then
    autorefresh = Range("F1").Value
    Sheets("WorksheetB").PivotTables("PivotTable1").RefreshTable
    Sheets("WorksheetB").PivotTables("PivotTable2").RefreshTable
End If
End Sub

These two groups of codes worked pretty well. However the only problem I face now is that whenever I save and reopen the workbook and enter new data in WorksheetA (but NOT in Column Z), the message box pops out even when the conditions for both worksheet_calculate are still not met. After this first msg box pop-out, everything returns normal again.

I'd appreciate it if anyone can shed some light to me on the codes that I'm using, and go through with me on what is wrong. I mostly referenced and combined the codes from various forums. I intend to replace the msg box with auto email sending capability whenever there is an out of control situation in the future. Therefore, if the worksheet_calculate function kept firing by itself whenever someone reopens the workbook, then false alarms will kept being sent to my email.

Thanks for your time reading through my questions. I appreciate it.

Answer 1

The reason that code fires after the workbook re-opens each time is because the static variables autorefresh and countvalue reset back to empty every time the workbook is open, so the IF block logic will always result in True on the first calc after the workbook is open, firing the rest of the lines inside the block.

One way around this is to store the counts of cell D1 in Worksheets B, C ... etc. in a separate sheet.

So for example, if you make a sheet at the back of the workbook and call it reference, and make row 1 a header row, with WorksheetA, WorksheetB, WorksheetC, Worksheet D ... as headers, you can then use row 2 to store the counts of each sheet (cell F1 for WorksheetA, cell D1 for the rest). Furthermore, if you define a name for where each value is stored, based on the Worksheet name, you can make your code more functional and uniform (see code below.)

Then, in your code, you can compare the current value on each sheet against what was stored last. Also, in your code, when the value does end up being different, you store the new value in those same cells.

That said, your Worksheet_Calculate code for WorksheetA becomes:

Private Sub Worksheet_Calculate()

If Worksheets("reference").Range(Me.Name).Value2 <> Me.Range("F1").Value2 Then
    Worksheets("reference").Range(Me.Name).Value = Range("F1").Value2
    ThisWorkbook.RefreshAll 'will refresh all pivot tables in the workbook
    'Sheets("WorksheetB").PivotTables("PivotTable1").RefreshTable
    'Sheets("WorksheetB").PivotTables("PivotTable2").RefreshTable
End If

End Sub

Then, the Worksheet_Calculate code for all other sheets becomes:

Private Sub Worksheet_Calculate()

 If Worksheets("reference").Range(Me.Name).Value2 <> Me.Range("D1").Value2 Then
    Worksheets("reference").Range(Me.Name).Value = Me.Range("D1").Value2
    MsgBox "You have new out of control situation located in " & me.Name
End If

End Sub

One last thing: Make sure to enter values in the reference sheet before closing the workbook after making the changes. Otherwise, when you open the workbook the very first time the code will fire, because the values in the reference sheet will be Null and the If block will result in True again.