can OnEntry be limited to certain cells only??

[Chief Wiggums]

I can use OnEntry to trigger a macro when any cell on a given sheet is edited --

Sub Auto_Open()
Worksheets("Sheet1").OnEntry = "TriggeredMacro"
End Sub

Is there a way to limit the triggering to just certain cells? so that only editing in those cells initiates the macro?

Thanks in advance ...

[Jim May]

example from Help:

Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If




"Chief Wiggums" <[email protected]>
wrote in message
news:[email protected]...
>
> I can use OnEntry to trigger a macro when any cell on a given sheet is
> edited --
>
> Sub Auto_Open()
> Worksheets("Sheet1").OnEntry = "TriggeredMacro"
> End Sub
>
> Is there a way to limit the triggering to just certain cells? so that
> only editing in those cells initiates the macro?
>
> Thanks in advance ...
>
>
> --
> Chief Wiggums
> ------------------------------------------------------------------------
> Chief Wiggums's Profile:
> http://www.excelforum.com/member.php...o&userid=16934
> View this thread: http://www.excelforum.com/showthread...hreadid=516531
>

[Chief Wiggums]

Hmmm ... I'm think this involves the Caller property, which can somehow be used to return the address of the cell whose editing triggered the macro.

If I had that, then that could serve as Range("rg1") in the example you've given
and it would work perfectly.

Thanks very much, Jim.

[Chief Wiggums]

OK, the following seems to work:

Set isect = Application.Intersect(Range(Application.Caller.Address), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

In this example, Application.Caller.Address returns the address of the cell which was edited. So one can test and then act, depending on whether that cell was a specified cell or not.

THanks again, Jim.

[Jim May]

Thanks for the feedback

"Chief Wiggums" <[email protected]>
wrote in message
news:[email protected]...
>
> OK, the following seems to work:
>
> Set isect = Application.Intersect(Range(Application.Caller.Address),
> Range("rg2"))
> If isect Is Nothing Then
> MsgBox "Ranges do not intersect"
> Else
> isect.Select
> End If
>
> In this example, Application.Caller.Address returns the address of the
> cell which was edited. So one can test and then act, depending on
> whether that cell was a specified cell or not.
>
> THanks again, Jim.
>
>
> --
> Chief Wiggums
> ------------------------------------------------------------------------
> Chief Wiggums's Profile:
> http://www.excelforum.com/member.php...o&userid=16934
> View this thread: http://www.excelforum.com/showthread...hreadid=516531
>