How can I monitor my spreadsheet continuously?

JJ59

New Member
Joined
Jul 30, 2002
Messages
14
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
 

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
You can write a macro that executes automatically, say every five seconds. Search for OnTime in this forum and in Excel-VBA help.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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