Tracking cell changes in realtime with timer alert

[kesvel]

Hi

Need you expert Excel help, hope this is easily done, very limited excel knowledge please advise in detail.

I want to track a cell change which occurs in real-time and raise some sort of audible notification or pop-up if the cell does not update for a predetermined period eg. has not updated in the last 2 minutes make a sound or send a pop-up. User should be able to change this value as needed.

Thanks in advance

 

[Legacy 98055]

Not a problem at all. How exactly is your cell changing? DDE? If so, is there an actual DDE link in the cell? If so, what is it? Paste it here. I need to know how your cell value is actually being updated. Also, will you be watching other cells simultaneuosly>

 

[kesvel]

Its a trading application that allows you to use excel spreadsheets. According to the manual it uses: "The cell now hosts the OLE link to Excel"

All that is done, is a copy of the cell of the trading application and then a special paste into the excel spreadsheet which results in the link below. When the value in the trading application changes it automatically updates the excel spreadsheet. So I basically want to setup the excel spreadsheet to generate a sound when the value of a single cell does not change for a specified time period. A number of cells will be monitored, each having a one to one relationship with the respective trading application value.

=Trade.Document|'C:\tt\x_trader\TTCONFIG\GM\Trade 1.ttt'!'\Item#14C4A18'

Thank you for your time looking into this, much appreciated.

 

[Legacy 98055]

Ok.

Seeing that I do not have access to your trading application, there is no way that I can test my solution. Download the following workbook.

When you open the workbook, this procedure will run.

Private Sub Workbook_Open()
    ThisWorkbook.SetLinkOnData "Trade.Document|C:\tt\x_trader\TTCONFIG\GM\Trade 1.ttt!\Item#14C4A18", "TestLink"
End Sub

See the "SetLinkOnData" method in VBA help for more info.

The link you provided is located in cell A1. When it updates, you should recieve a messagebox stating, "Link Updated...". Please reply with the results so that I know which direction to take from here.

User should be able to change this value as needed.

Please expound. By what means do you intend to retrieve and store the users configuration settings? A userform? Within worksheet cells? Please respond with a detailed explanation...

 

[kesvel]

Thanks

Sorry about the late reply, Thanks will try solution, essentially want this pop-up if the cell (a1) does not update within five minutes (it would be nice if the user can change this value using another cell in the spreadsheet) eg. cell A1 has not updated in the last 5 minutes then show pop-up or play sound. The 5 minute value can be calcuated using a reference to the last update received or using the pc time etc.

 

[tusharm]

Re: Thanks

The update check interval is assumed to be specified in minutes in Book1 Sheet1 Range H1. Change the one line in the code if the cell is some other.

In the code module of the worksheet that contains the updated link (cell A1), enter

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
    LastDDEUpdateTimeStamp = Now
    'MsgBox "_Change event triggered at " & Format(Now(), "hh:mm:ss")
    End Sub

In a standard module, enter

Option Explicit

Public LastDDEUpdateTimeStamp As Date
Dim NextCheckTime As Date
Sub checkLastUpdateEvent()
    Dim UpdateInterval As Date
    UpdateInterval = TimeSerial(0, _
        Workbooks("book1").Sheets("sheet1").Range("h1").Value, _
        0)
    If LastDDEUpdateTimeStamp <> 0 _
            And Now() >= LastDDEUpdateTimeStamp + UpdateInterval Then
        MsgBox "Oops! No update since " & LastDDEUpdateTimeStamp
    Else
        NextCheckTime = _
            IIf(LastDDEUpdateTimeStamp = 0, Now, LastDDEUpdateTimeStamp) _
            + UpdateInterval
        Application.OnTime NextCheckTime, "checkLastUpdateEvent"
        End If
    End Sub
Sub stopChecking()
    Application.OnTime NextCheckTime, "checkLastUpdateEvent", , False
    End Sub

To start the checks, run the checkLastUpdateEvent procedure. To stop the checks, run the stopChecking procedure.

The above lightly tested with a web query as the link.

Also, in a "production" system I would never put code in a workbook containing data. However, the above is as good as it can get given the forum being used but it is not "professional grade" from a scalability / maintainability / deployment perspective.

Sorry about the late reply, Thanks will try solution, essentially want this pop-up if the cell (a1) does not update within five minutes (it would be nice if the user can change this value using another cell in the spreadsheet) eg. cell A1 has not updated in the last 5 minutes then show pop-up or play sound. The 5 minute value can be calcuated using a reference to the last update received or using the pc time etc.

 

[kesvel]

How do I implement

Hi Tusharm,

I should have said from the outset that I do not have much excel experience, but can find my way around, how do copy and paste the code in excel 2003. Creating the link for the updating cell A1 is no problem however unsure how I paste the code into the workbook along with the timer value (h1 range).

Thanks

 

[Legacy 98055]

The change event is not going to fire when your link updates. You can try Tusharm's example by downloading the workbook below...

Tusharm'sExample.zip

If Tusharm's example does not work, check out the SetLinkOnData method that I posted earlier. The second workbook attached to my post has your link in cell A1. When it updates, you may or may not get a popup messagebox. I need to know if this method works with your link or not. We can then go from there...

1139120_Tracking cell changes in realtime with timer alert.zip

Another way would be to simply use an application.ontime polling loop, by which you could check the range(s) containing your links every two minutes. You could start checking them at the open of your workbook.

 

[kesvel]

Thanks guys let me try that and come back to you

Thanks guys let me try that and come back to you