Tracking cell changes in realtime with timer alert

kesvel

New Member
Joined
Sep 27, 2006
Messages
5
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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>
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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...
 
Upvote 0
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.
 
Upvote 0
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
Code:
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
Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thanks guys let me try that and come back to you

Thanks guys let me try that and come back to you
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top