Worksheet_Change() not being called when expected

Posted by Cathy on May 24, 2001 11:58 AM

I'm assuming that Worksheet_Change() should
be getting called when my worksheet updates from
external links.... but the subroutine is only
getting called when I manually change a cell.

Should the subroutine be getting called by updates
from external links? If so, any ideas on why
my code is not getting called?

thanks in advance

Posted by Dave Hawley on May 24, 2001 8:52 PM

Hi Cathy

Although the help sais the Event is fired via external sources, this is not usually the case. You can sometimes work around this though with this simple method.

Lets say you want the event fired whenever a cell in the range A1:A10 is updated. In cells B1 put =A1 and copy down to B10. Then use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
Dim rWatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub

Set rWatchRange = Range("B1:B10")

If Not Intersect(Target, rWatchRange) Is Nothing Then
MsgBox Target.Offset(0, -1).Address & " has just changed"
End If

Set rWatchRange = Nothing
End Sub

Dave

OzGrid Business Applications