Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Event Sub when A1 < B1
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 -- Paul Kraemer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Event Sub when A1 < B1
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. "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Event Sub when A1 < B1
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 "Paul Kraemer" wrote in message ... 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 -- Paul Kraemer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Event Sub when A1 < B1
Bob has a small typo:
If Target.Value2 < Target.Offset(0, -1).Value2 Then (That shift key is a killer <vbg.) Bob Phillips wrote: 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 "Paul Kraemer" wrote in message ... 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 -- Paul Kraemer -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger Event Sub when A1 < B1
It is when you are as poor a typist as I, the shift stays down far too long.
I am forever typing ACtive Bob "Dave Peterson" wrote in message ... Bob has a small typo: If Target.Value2 < Target.Offset(0, -1).Value2 Then (That shift key is a killer <vbg.) Bob Phillips wrote: 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 "Paul Kraemer" wrote in message ... 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 -- Paul Kraemer -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using autofilter to trigger an event | Excel Programming | |||
Event Trigger | Excel Programming | |||
Trigger Event Code | Excel Programming | |||
Trigger Event | Excel Programming |