"Target" equivalent in Worksheet_Calculate (DDE li

[ljtong]

Hello Everybody,

Is it possible to get the address of the cell which has changed if it is a DDE link? Similar to what the Worksheet_Change has..?

I'm asking because I have 60 linked cells, recording 60 similar things and I would prefer not to use 60 conditions if I can get the address of only the cell that changed.

Thanks

Tong

 

[parry]

Afraid not, the calculate event has no arguments. Are you using QueryTables to connect to a db? If so there is a BeforeRefresh and AfterRefresh event where you could store the values beforehand and see if changes were made after the refresh.

 

[ljtong]

I'm using the DDE link to look at a AllenBradley PLC-5
Any other suggestions?
Otherwise I'll just make up 60 conditions then

Thanks

 

[parry]

Heres one way to fudge it. Name a blank sheet Calc then copy the values of the calculated cells into the sheet (note it has to be the values, not a formula pointing to the cells). Amend the ranges to suit...

Private Sub Worksheet_Calculate()
Dim Rng As Range, c As Range, Arr() As Variant, i As Long, e As Variant

'Your range to monitor
Set Rng = [C1:C9]

'Populate an array of changed cells
For Each c In Rng
    i = i + 1
    With Sheets("Calc")
    If c.Value <> .Cells(i, 1) Then
        On Error Resume Next
        ReDim Preserve Arr(UBound(Arr) + 1)
        If Err.Number <> 0 Then ReDim Arr(0)
        Set Arr(UBound(Arr)) = c
        On Error GoTo 0
    End If
    End With
Next c

'Return result
If Not IsEmpty(Arr) Then
    For Each e In Arr
    MsgBox "Changed cell address was " & e.Address
    Next e
End If

'Copy values of cells to Calc sheet ready for next recalc
Rng.Copy
Sheets("Calc").[A1].PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

 

[ljtong]

so at the end of your code i can use e.address to get the address of the changed cell? I will try it later thanks

Maybe let me explain my intention:

I am monitoring 60 bins, therefore in 60 rows i have links to the Full_Status for each bin.

When a bin becomes full (Full_status = 1), the time and date it became full is logged and a Full counter for that bin is incremented.

I hoped to know the address of the cell changed because I now have to run 59 more checks (since I don't know which bin changed) in worksheet_calculate than it needs to.

 

[parry]

Im not familiar with AllenBradley PLC-5 but the variable e is a cell range, so instead of a message (which was just an example) you would put your code to do whatever action for the cell. So you can get the value of the cell by using e.value etc so think of e as Range("whatever"). Because this is looping through values that were changed you dont need 60 conditions just whatever happens when a cell is changed in between the "For Each e In Arr" and "Next e" lines.

So you only need to ...

1) replace the line "Set Rng = [C1:C9]" with the range you want instead of C1:C9 eg A1:A60 or whatever.

2) replace the "MsgBox "Changed cell address was " & e.Address " line in between the "For Each e In Arr" and "Next e" lines with the action you require to happen

 

[Swamp Thing]

Suppose that A1:A60 is the range you want to monitor. Let's create a column, say E1:E60, into which you copy the "old" values from A1:A60. Next, in the column F1:F60 we type in a boolean formula like "=(A1=E1)" and fill down. This will give you TRUE in each cell of F1:F60 because each "E" is equal to the corresponding "A" cell.

Now your DDE link operates and changes, say, A7 to a new value. So you get "FALSE" in the cell F7 because the old value E7 is no longer equal to the new value A7.

Now lets set up a "pointer" cell with the formula =MATCH(FALSE,F1:F60,0). This cell will now contain the row number in which the change occurred (i.e. 7 in our example).

Your worksheet_calculate code has to use this cell to do whatever it needs to do, and then re-copy the "new" value from A7 into the "old" list in E7, before exiting to wait for the next event. If you expect more than one value change per event, you will have to loop back to repeat the process untill the pointer cell shows no match.