Worksheet 'on Change' event
 

How do I refer to the cell I just changed?

I need to run the on change event but only if the cell I just changed was
one specific cell.

Worksheet 'on Change' event
 

Hi Damien,

The way to refer to the changed range (look out it can be one or multiple
cells) is by using the variable Target, which is declared as the argument
for this event procedure.
This example may help you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
MsgBox "Hello!"
End Sub

Regards,
KL


"Damien McBain" wrote in message
...
How do I refer to the cell I just changed?

I need to run the on change event but only if the cell I just changed was
one specific cell.

Worksheet 'on Change' event
 

One way:

Assume your "specific cell" is B4:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "B4" Then
'Do stuff
End If
End Sub

In article
,
"Damien McBain" wrote:

How do I refer to the cell I just changed?

I need to run the on change event but only if the cell I just changed was
one specific cell.

Worksheet 'on Change' event
 

or a slightly shorter version:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$4" Then
'Do stuff
End If
End Sub

:-)

Regards,
KL


"JE McGimpsey" wrote in message
...
One way:

Assume your "specific cell" is B4:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "B4" Then
'Do stuff
End If
End Sub

In article
,
"Damien McBain" wrote:

How do I refer to the cell I just changed?

I need to run the on change event but only if the cell I just changed was
one specific cell.

Worksheet 'on Change' event
 

Maybe this will help. Let's say the specific cell you want to watch for is
C5. You could check the address of the range that was just changed (first
macro). It is not very flexible in that if you insert any rows/columns or
move cell C5, it will no longer work. Also, if you changed a range (say
C3:C6) it will not recognize cell C5 was changed. If either of these issues
are problematic in your situation, the second one assumes I've named cell C5
"NamedRange" and tests to see if this cell intersects the range that was
changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$5" Then MsgBox "hello"

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range

Set Isect = Application.Intersect(Target, Range("NamedRange"))
If Not Isect Is Nothing Then MsgBox "hello"

End Sub


"Damien McBain" wrote:

How do I refer to the cell I just changed?

I need to run the on change event but only if the cell I just changed was
one specific cell.

Worksheet 'on Change' event
 

Or, without the slower function call:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then _
MsgBox "Hello!"
End Sub



In article ,
"KL" wrote:

The way to refer to the changed range (look out it can be one or multiple
cells) is by using the variable Target, which is declared as the argument
for this event procedure.
This example may help you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
MsgBox "Hello!"
End Sub

Worksheet 'on Change' event
 

Not the same, I am afraid. The "slower function call" covers both the
change-in-the-monitored-cell-only and
multiple-cells-where-the-monitored-cell-is-one-of-them, while Target.Address
= "$B$4" only covers a one-cell change. And, honestly, you can't tell the
speed difference in this context (unless you measure it with special
software), so speed is not a factor in this equation.

Regards,
KL


"JE McGimpsey" wrote in message
...
Or, without the slower function call:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then _
MsgBox "Hello!"
End Sub



In article ,
"KL" wrote:

The way to refer to the changed range (look out it can be one or multiple
cells) is by using the variable Target, which is declared as the argument
for this event procedure.
This example may help you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
MsgBox "Hello!"
End Sub

Worksheet 'on Change' event
 

In article ,
"KL" wrote:

Not the same, I am afraid. The "slower function call" covers both the
change-in-the-monitored-cell-only and
multiple-cells-where-the-monitored-cell-is-one-of-them, while Target.Address
= "$B$4" only covers a one-cell change. And, honestly, you can't tell the
speed difference in this context (unless you measure it with special
software), so speed is not a factor in this equation.

Huh?

What's not the same? What does Target.Address = "$B$4" have to do with
it?

Worksheet 'on Change' event
 

The slower function call that JE was referring to was: Me.[a1]

It turns out that [a1] is slower than range("a1").

(Nothing to do with the Intersect() part of your code.)


KL wrote:

Not the same, I am afraid. The "slower function call" covers both the
change-in-the-monitored-cell-only and
multiple-cells-where-the-monitored-cell-is-one-of-them, while Target.Address
= "$B$4" only covers a one-cell change. And, honestly, you can't tell the
speed difference in this context (unless you measure it with special
software), so speed is not a factor in this equation.

Regards,
KL

"JE McGimpsey" wrote in message
...
Or, without the slower function call:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then _
MsgBox "Hello!"
End Sub



In article ,
"KL" wrote:

The way to refer to the changed range (look out it can be one or multiple
cells) is by using the variable Target, which is declared as the argument
for this event procedure.
This example may help you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
MsgBox "Hello!"
End Sub

--

Dave Peterson

Worksheet 'on Change' event
 

OK, sorry - got it wrong.

KL

"JE McGimpsey" wrote in message
...
In article ,
"KL" wrote:

Not the same, I am afraid. The "slower function call" covers both the
change-in-the-monitored-cell-only and
multiple-cells-where-the-monitored-cell-is-one-of-them, while
Target.Address
= "$B$4" only covers a one-cell change. And, honestly, you can't tell the
speed difference in this context (unless you measure it with special
software), so speed is not a factor in this equation.

Huh?

What's not the same? What does Target.Address = "$B$4" have to do with
it?