Trigger Event Sub when A1 <> B1

[Paul Kraemer]

Hi,

Let's say I have values in two cells, A1 and B1.

A1 contains a fixed value. B1 displays a value from a DDE connection to a
communication server that pulls data from a particular device. The value in
B1 can change at any time.

Under normal circumstances, B1 should always be equal to A1. If B1 changes
and is no longer equal to A1, this indicates an alarm condition and I would
like this condition to trigger a VBA Sub which will include some code to
process the alarm.

So far, the only way I think of doing this is by responding to the
Worksheet_SelectionChange event, in which I would check to see if the Target
range was B1, and if so, I would run my error processing code.

I am afraid to do this because there will be other cells on my worksheet
that will be changing very frequently (probably once per second). I am
afraid that this will cause the SelectionChange event to be called so often
as to adversely affect performance of the worksheet. I am not sure that this
will be an issue because unless the Target Range is B1, the SelectionChange
event will not actually do anything, but it will still be running and have to
check the Target Range once per second.

I can give this a try, but first, I just thought I'd ask if there is a
better way for me to trigger an event any time B1 <> A1 without having to use
Worksheet.SelectionChange

Any help will be greatly appreciated.

Thanks,
Paul

 

[Mike H]

Hi,

selectionchange is the wrong event. It fires when a different cell is
selected in the worksheet. You should (probably) use worksheet_calculate and
I doubt you will experience problems because the event fires anyway every
time the sheet calculates.

Now if you then have as the first line of the event code any unnecessary
process is reduced to a minimum

Private Sub Worksheet_Calculate()
If Range("A1") = Range("B1") Then Exit Sub
'Do things
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bob Phillips]

Test it

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B1")) Is Nothing Then

If Target.Value2 <> Target.Offset(0, -1) > Value2 Then

MsgBox "Ooops"
End If
End If
End Sub


HTH

Bob

 

[Dave Peterson]

Bob has a small typo:

If Target.Value2 <> Target.Offset(0, -1).Value2 Then

(That shift key is a killer <vbg>.)

 

[Bob Phillips]

It is when you are as poor a typist as I, the shift stays down far too long.
I am forever typing ACtive

Bob