PDA

View Full Version : Detecting a cell change



paulked
02-08-2011, 11:25 AM
Hi all.

I'm trying to run a routine when the value of cell I104 changes. This value is either 1 or 0 depending on the state of a sensor which is automatically updated via an OLE product.

I'm using

Private Sub Worksheet_Change(byval target as range)
If target.address = ("I104") then Light54
End Sub

but I can't get it to work.

Any ideas?

Cheers

Paul Ked

mancubus
02-08-2011, 11:49 AM
hi.
try:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$104" Then Run "Light54"
End Sub

paulked
02-08-2011, 12:27 PM
Thanks for reply. I have tried that, without success.

mikerickson
02-08-2011, 01:06 PM
It is possible that the OLE upload doesn't trigger a Change event.

If that is the case, one work-around is to put a formula that refers to the key cells (e.g. =COUNTA(I:I)) in an un needed cell and use the Calculate event.

One difficulty with this approach is that you don't know which cell has been changed, so you have to provide that part of the coding.

paulked
02-08-2011, 01:44 PM
Thanks for that.

I did try the example of the Worksheet_Change from the Help (Changes font colour) whilst the OLE was running and that did work which, I assumed, meant that the Worksheet_Change event was monitoring the sheet.

But I'll give it a go on Thursday (next visit to site).

Cheers

Paul Ked