Custom sound when a cell value changes.

[Nishant S]

I have a software which gives me live stock prices in excel and I have added all my analysis to that file so that I can get Long and Short Calls for the scrips I analyze. Now, there are around 200 companies that I analyse and the calls appear as the stock price change and reach my buying or selling levels.

I need my excel to say the companies name which is in cell C9 when "Long" / "Short" text appears in my cell M9/N9 which has my analysis formulas. Current market price is in E9

Also I need this for a range as I mentioned I have 200 companies.

Please see the example file that I have uploaded. Workarea has all the formulas. If the Current Stock price changes in column E, Column M and N are affected and might give me Long or Short Promt.
What I need is whenever a new long or short appears I want excel to say the companies name for all the companies

I really hope you can help me with this.

 

[NARAYANK991]

Hi ,

We can make use of an event procedure called Worksheet_Change , which will run when ever a user makes changes to any worksheet cell / range.

However , changes which result from formulae will not make this procedure run.

We can make use of a Worksheet_Calculate procedure , but this runs when ever a calculation takes place anywhere in the worksheet , and will not tell us which cell / range triggered the calculation.

Can you explain what the following means :

whenever a new long or short appears

For example , suppose M9 already has the text Short , and N9 already has the text Long ; when will they get a new Long or Short ?

Narayan

 

[Nishant S]

Hi ,

We can make use of an event procedure called Worksheet_Change , which will run when ever a user makes changes to any worksheet cell / range.

However , changes which result from formulae will not make this procedure run.

We can make use of a Worksheet_Calculate procedure , but this runs when ever a calculation takes place anywhere in the worksheet , and will not tell us which cell / range triggered the calculation.

Can you explain what the following means :

For example , suppose M9 already has the text Short , and N9 already has the text Long ; when will they get a new Long or Short ?

Narayan

Hi Narayan

Thanks a lot for your reply

M9 and N9 already have long or short in it
but If the share price moves away from a particular range it will disappear and once they come again in that range Long/Short will reappear. When that happens I want excel to say the stocks name.

I did try Worksheet_Change function but the problem is I can do it for a particular row. Dont see how to apply it to my range of Data.

And the problem with Worksheet_Calculate is it goes on saying the name as and when anything changes so its not useful.

 

[NARAYANK991]

Hi ,

I have already mentioned that the Worksheet_Change procedure cannot be used to detect changes in worksheet cells due to formulae.

Can you explain what actually happens in the worksheet tab named Streaming_Stock_Watch ?

We can run a procedure every time the tab Streaming_Stock_Watch is updated , which will look at the current status of all the cells M9:N29 (or what ever is the last cell of data in your working file) , compare it with the previous status , and detect whether a change has taken place.

Narayan

 

[Nishant S]

Hi ,

I have already mentioned that the Worksheet_Change procedure cannot be used to detect changes in worksheet cells due to formulae.

Can you explain what actually happens in the worksheet tab named Streaming_Stock_Watch ?

We can run a procedure every time the tab Streaming_Stock_Watch is updated , which will look at the current status of all the cells M9:N29 (or what ever is the last cell of data in your working file) , compare it with the previous status , and detect whether a change has taken place.

Narayan

Hey,

I dont think we can do anything in the Streaming_Stock_Watch as it gets updated directly from the trading software. I am not aware on how that works

 

[NARAYANK991]

Hi ,

Then I do not see how we can do anything.

The main issue is to know when to run a procedure which will compare current status with previous status and output the appropriate result.

How exactly does a change take place ?

Will a cell which is currently having Long / Short in it , first change to FALSE , and only then , sometime later change again to either Short / Long ?

Narayan

 

[Nishant S]

Ok...
Let me try this again

Please forget the previous example and consider the file attached here instead.

Now here Long and short will be generated when the current market price comes close to levels in column E and F

As soon as the price comes within the 1% range of the Column E and F calls are generated in Column J and M Respectively.

What I am looking for is when a new Long or Short Appears Excel says the stock name.

I have tried this Already

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$9" Then
If Range ("J9"),Value = "Long" Then
Application.Speech.Speak (Range("B9").Value)
End If
End If
End Sub

This Works for a particular cell but not a range

But even if i duplicate the same procedure for the other cells it gives an error of Change Object Clause.

I hope the problem is more clear now

 

[NARAYANK991]

Hi ,

I am sorry but I have explained that the Worksheet_Change procedure cannot be used because it will not work.

If you think it works , please feel free to use it.

Narayan

 

[Nishant S]

Hi ,

I am sorry but I have explained that the Worksheet_Change procedure cannot be used because it will not work.

If you think it works , please feel free to use it.

Narayan

It dosnt work for the range. Is there any other way?

 

[NARAYANK991]

Hi ,

Unless we can understand how and when the Streaming_Stock_Watch tab updates , there is nothing much that can be done.

If you want , we can use the Worksheet_Calculate procedure , and see how it works.

I will take some time to look into this.

Narayan

 

[Nishant S]

Hi Narayan,

Any update on the issue?