Excel ranges, events and naming

Posted by Gogi on February 15, 2001 4:33 AM

Hi,

I want to raise an event everytime anything in a range changes(is calculated).... I named my range and want to know what range(name) triggered a change/calculate event...

Best Regards,

Gogi

Posted by Dave on February 15, 2001 6:49 AM

Hi Gogi

I believe you will need to use the Workbook_SheetChange event for this as the Calculate event will not allow you to trace the trigger. So try the code below:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Dependents = Range("MyRange") Then
MsgBox "Hello"
End If
Exit Sub
Done:
End Sub

This will only show the message box if the changed cell will effect a formula in the named range "MyRange"

Dave
OzGrid Business Applications