How can I monitor my spreadsheet continuously?

[JJ59]

Hi,

I'd like to write a macro that can monitor my spreadsheet continuously. Here's what I want:
I can pull the dynamic stock price from my quote system to excel spreadsheet. So I'd like to have excel to alert me when my stock price is going down (or up).

I try to write a macro using "loop" but it simply doesn't work as my computer will go down.

all your advice will be appreciated

Thanks in advance,

JJ

 

[Swamp Thing]

You can write a macro that executes automatically, say every five seconds. Search for OnTime in this forum and in Excel-VBA help.

 

[PaddyD]

I assume that not all changes are significant...In which case, you could have a worksheet_change macro to alert you when certain thresholds / conditions are met.

This will tell you when a value is > than a threshold:<pre>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub


If Range("B1").Value > 10 Then
MsgBox "Moved Up"
End If
End Sub</pre>

or you could check if the new value has increased from the old value by a certain percentage...

Paddy
This message was edited by PaddyD on 2002-08-26 18:37

 

[JJ59]

Thank you very much for both of your replies.

PaddyD, I've tried your suggestion and modified it to meet my requirement. Unfortunately it does not work.
Questions: Why should I use intersect method here? also, I know it's extremely silly, but should I alway put private sub in ThisWorkbook?

Thanks again,

JJ

 

[Swamp Thing]

One thing that is not clear from the above is, have you already got the data to update itself at regular intervals, or do you need help to do this? If you already have the data updating automatically, then how? Perhaps the check-for-condition-and-alert-if-changed routine can be inserted at the end of your update routine.

If you have not yet reached the stage where the data is refreshed automatically, then consider using the Application.OnTime method (search on this forum; do post back if required).

 

[PaddyD]

On 2002-08-26 20:10, JJ59 wrote:
Thank you very much for both of your replies.

PaddyD, I've tried your suggestion and modified it to meet my requirement. Unfortunately it does not work.

how about posting what you've come up with

Questions: Why should I use intersect method here?

The code I posted was a check for a change in the value of B1. But 'cos it was in a worksheet_change sub, if we didn;t add another check to check that it was B1 that changed, then once B1 was over the threshold value, you'd get the msgbox everytime anything on the worksheet changed - not what you want!

also, I know it's extremely silly, but should I alway put private sub in ThisWorkbook?

Not that I know of - this would be fine in a sheet's code


Thanks again,

JJ

 

[Ivan F Moala]

I believe if your Data is live "Stock feed" then the change event will not fire..you will need to link your cells up to some other cell and use the Calculate event to monitor these cells.

 

[PaddyD]

well that would explain it - really must keep my paws out of VBA questions!

Paddy

 

[Swamp Thing]

Hi JJ,
can you tell me how you set up the live feed? I'd like to have a crack at this.

 

[JJ59]

Thank you all for your precious advice.

Thanks to this forum, I got a link regarding the arrangment of procedure.

I end up using ontime method to monitor my spread sheet. I wrote a small procedure (timer) then I wrote another procedure including my code and timer. It works great!

I got a another question, though: I put two procedures (Auto save and start "timer") in private sub workbook_open but it seems that this private sub can only recognize one sub. i.e. the timer in the private sub just doesn't activate. Is it correct?

P.S. Swamp thing: thank you for your help. my quote systerm can update the stock quote dynamically.

Thanks again for your advice.

JJ