Check changes on a sheet
 

I hope somebody can help me out on this one, because I am seriously
stuck and quite desperate too.
I have a sheet full of formulas that update the sheet in real time
with info from bloomberg. Unfortunately, every now and then the
bloomberg service crashes and the screen stops updating, but nobody
realizes it. So the question is:
Is there a way of writing a macro that every 2 minutes will see if
there have been any changes (in the last 2 minutes) on the sheet, and
if there aren't any have an alert pop-up message appear? Possibly with
an alert sound too (default windows or whatever, I don't care)?
Thanks for any help you guys out there can give me.
Lorenzo

Check changes on a sheet
 

Hi Lorenzo,

this may help ...
http://support.microsoft.com/default...nt/onevent.asp

Guido

HoloGuides
Made for You
http://HoloGuides.com/programming/ExcelVBA

"Lorenzo" schrieb im Newsbeitrag
m...
I hope somebody can help me out on this one, because I am seriously
stuck and quite desperate too.
I have a sheet full of formulas that update the sheet in real time
with info from bloomberg. Unfortunately, every now and then the
bloomberg service crashes and the screen stops updating, but nobody
realizes it. So the question is:
Is there a way of writing a macro that every 2 minutes will see if
there have been any changes (in the last 2 minutes) on the sheet, and
if there aren't any have an alert pop-up message appear? Possibly with
an alert sound too (default windows or whatever, I don't care)?
Thanks for any help you guys out there can give me.
Lorenzo

Check changes on a sheet
 

Thank you both for your help, but I'm afraid I didn't make myself
quite clear.
My problem is not having the macro run every two minutes, but to have
excel compare the sheet to what it was like 2 minutes before. Hence
the code should run every two minutes something like

If "value in all cells is different from 2 minutes ago (or last time
macro was run)" then end sub
else msgbox "Verify connection to updating service"

I'm pretty much stuck with the comparing the the present excel sheet
with what it looked like 2 minutes before. It probably involves saving
a copy of the sheet somewhere, maybe on a new hidden sheet, at the end
of the macro, to be used by the next macro cyle, so the code would be
something like

if "sheet1 = hiddensheet" then msgbox "Verify connection etc"
else sheets("sheet1").select
selection.copy
sheets("hiddensheet").activate
paste values
end sub

this way the macro would actually have a sheet to compare sheet1 to,
which is maybe easier than having excel keep track of any changements.
Still, I'm more than puzzled by the comparing part. Unless there is a
way of having excel verify at least one cell is modified every two
minnutes, I'm quite convinced this is the way to go... So does anybody
have an idea to compare the two sheets?

Thanks again for any help you can give.

Lorenzo

Check changes on a sheet
 

In your ontime event, save a copy of the sheet in an array. When the event
runs again, get a copy in an array again - then compare the arrays - repeat
until cooked.

here is some untested pseudo code that might give you some ideas

Public newTime as Double

Sub OnTimeEventMacro()
Static varrold as variant
newtime = now + timevalue("00:02")
if isempty(varrold) then
varrold = ActiveSheet.UsedRange.Value
Application.OnTime newTime,"OnTimeEventMacro"
exit sub
End if
varrnew = Activsheet.UsedRange
bChanged = False
for i = lbound(varrnew,1) to ubound(varrnew,1)
for j = lbound(varrnew,2) to ubound(varrnew,1)
if varrnew(i,j) < varrOld(i,j) then
bchanged = True
exit for
end if
Next
if bChange = True then
exit for
end if
Next
if bChanged = True then
' schedule new check
varrold = varnew
Application.OnTime newTime,"OnTimeEventMacro"
End If
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Look at Chip Pearson's example of using Application.OnTime

http://www.cpearson.com/excel/ontime.htm


Regards,
Tom Ogilvy

"Lorenzo" wrote in message
m...
I hope somebody can help me out on this one, because I am seriously
stuck and quite desperate too.
I have a sheet full of formulas that update the sheet in real time
with info from bloomberg. Unfortunately, every now and then the
bloomberg service crashes and the screen stops updating, but nobody
realizes it. So the question is:
Is there a way of writing a macro that every 2 minutes will see if
there have been any changes (in the last 2 minutes) on the sheet, and
if there aren't any have an alert pop-up message appear? Possibly with
an alert sound too (default windows or whatever, I don't care)?
Thanks for any help you guys out there can give me.
Lorenzo