Trigger Macro

[OptionTrader]

I�m trying to get my computer to e-mail me (to my mobile) a periodic market report when I�m away from my screen. For example, option prices, daily portfolio P&L and other bits and pieces. I have the code to send e-mails and use a function to call it. For example;

Sub MyMacro()
SendEmail
End Sub

MyFunction()
Call MyMacro
End Function

In cell A2 I will write

=IF(A1=20,MyFunction(),��)

So if cell A1 hits 20 the function will call the macro which will then send me an email. Here is the problem;

How do I instruct to send JUST ONE e-mail and then no more ? Because the way it�s set up at the moment it will continue sending e-mails for as long as the value in cell A1 is 20. I�ve tried writing into the Sub to change the value of cell A1 to 0, but then when I run the procedure it gives a circular reference, which I suppose is logical. I'm stuffed !

[dbish]

I am not a professional programmer - just another finance guy who has run across something similiar.

How about putting a 0 in some cell outside of the range you print or email to your PDA (like AZ1). Then you can have your Send Email function increment with a line like
Range("AZ1").Select
ActiveCell.Value=ActiveCell.Value+1
and make the A2 formula something like
=IF(AZ1=0,IF(A1=20,MyFunction(),””),0)

This will require both conditions AZ1=0 and A1=20 to execute. Just initial AZ1 to 0 when the workbook opens for the first time of the day (Workbook.Open event).

Good luck

dbish

[OptionTrader]

dbish

Thanks. but unfortunately changing the value of cell AZ1 in the sub() and then using it's value to trigger the sub (via a function) will generate a circular reference.

[dbish]

Which macro did you put the Increment AZ1 code. If you put it in the MyFunction code it would logically bomb (I am guessing). Does it bomb if you put it in the Email code?

Also, how do you check for the Trigger hitting 20 - it is a continuous loop that checks every few seconds (minutes)?

Where do you get the prices for your trigger - do you tie to a feed like Interactive Brokers workstation app or some other live feed? Perhaps you can stick the test in the coding that gets you the price data.

[OptionTrader]

dbish

Which macro did you put the Increment AZ1 code.

I put it in the e-mail sub code. It then gives a circular reference when it runs.

Also, how do you check for the Trigger hitting 20 - it is a continuous loop that checks every few seconds (minutes)?

The trigger could be anything such as a pre-determined P/L or absolutely anything. I have a live feed which updates into Excel every time there is a price(s) change.

Where do you get the prices for your trigger

MyTrack

[dbish]

Well, you got me. I have no more suggestions. Somewhere there must be a non connected piece of code that could be interrupted with such a test but I don't see it.

Good luck

Dave

[OptionTrader]

Dave

Thanks for trying. I've found that if there's an easy and obvious answer someone will reply PDQ. It's the problem questions that tend to go unanswered.

Look in again - you never know...

[Norie]

Why are you using a function as a trigger?

Why not use a worksheet event like Change?

[OptionTrader]

Norie

Could you give me some pointers please ?

[OptionTrader]

Surely it can't be impossible to trigger a macro just once ?

[OptionTrader]

Guys

I really am stuck on this one. I'd be extremely grateful for any help. Even to be told it's impossible would be useful (then I'd move on).

TIA.

[OptionTrader]

I've assumed it's impossible.

Moving on...

[Halen]

hey don't give up!

why dont you try using an "If" function in MyMacro?

For example,

Global EmailSent as Boolean
EmailSent = False

then in the MyMacro add:

Sub MyMacro()
If EmailSent = false then
SendEmail
EmailSent = True
End Sub


or something like that? Im not really an expert but I have used a lot of If functions, so maybe this is the start of an idea that you could play around with?