Worksheet_Calculate() Problem

[yumbelie]

Right, I have a workbook containing three worksheets. Now, in each worksheet I need to use the Worksheet_Calculate() Method to cause an event to trigger, and some rows in that worksheet to be coloured. Now, this works fine for 2 of the 3 worksheets - but for some unknown reason, one of the worksheets Worksheet_Calculate() method is called whenever any of worksheets executes a caluclation. This has the effect of colouring the rows both in the correct sheet, and the faulty sheet - which is not what I want. Basically, any time you mark/colour any of the sheets rows, you cause this 3rd sheet to be affected. I have no idea why this happens, since the method is private. I use identical code in all 3 sheets, save for a few worksheet name changes (which I've checked to ensure they are correct) - and neither of the other 2 sheets effects each other. If I pull the Worksheet_Calculate() method out of Sheet3 (the faulty sheet) it's no longer affected. Seems that the Worksheet_Calculate() method is being called as if it were public - so whenever any worksheet executes it's own private Worksheet_Calculate, the faulty sheets method is also executed - which is very perplexing. The code I use is shown below:

Private Sub Worksheet_Calculate()



MsgBox ("DTC Costs&quot
'Worksheets("DTC Costs&quot.Activate

Set MySheet = Worksheets("DTC Costs&quot 'Swap if you are
'Set OtherSheet = Worksheets("Patient Delay&quot 'on DTC Sheet

crow = currentrow(ActiveCell.Address)
Dim tstring As String
'Create Address to compare target address against

tstring = Trim(Left(ActiveCell.Address, 3)) & Trim(Str(crow))

If ActiveCell.Address = tstring Then

If ActiveCell.Value = "Deactivate Record" Then

MySheet.Range(Cells(crow, 1), Cells(crow, 14)).Interior.ColorIndex = 15
'Call MarkRemoteRow(Int(crow), "Patient Delay", 3, "DTC Costs", 3, 15, "Deactivate Record&quot


ElseIf ActiveCell.Value = "No Action" Then

MySheet.Range(Cells(crow, 1), Cells(crow, 14)).Interior.ColorIndex = 0

End If

End If

Set MySheet = Nothing

End Sub

 

[combo]

Seems that changing your data you trigger to recalculate more than one sheet. That cause to execute Worksheet_Calculate() subs in recalculated shhets.

Some additional conditions in Worksheet_Calculate() sub of Sheet3 could help to avoid colouring if it is not desired (for example if it is not the active sheet)