Executing a macro based on worksheet change

**Rynofasho** · 03-04-2010, 12:12 PM

Hello all,

I currently have the following Macro --- it basically is looking at a session low price and increasing it by .0001 until it is equal to the session high. Here is the code:

Please Login or Register  to view this content.

What I want to do is stack a worksheet change event on top of this so that when the session low changes, the sheet dynamically updates the prices too. I don't know much VBA so I don't know how to write something else to call this. I saw examples of the worksheet change event, and I tried to implement them but I was unsuccessful. I tried this but I dont know the syntax or if I have to put this before my other macro on Sheet 1, in a separate module, etc (again, no VBA experience):

Please Login or Register  to view this content.

Not sure how to call my macro and how to stack these. Anyone have any ideas?

Last caveat I forgot to put is that D3 is actually a formula, not a static number, so I think I am not using the right sub.

**Leith Ross** · 03-04-2010, 01:18 PM

Hello Rynofasho,

I realize it has been awhile since you have posted. I have added code tags to your post as a courtesy this time, but please do so in the future. You should read the forum rules as there have been changes made to them.

**Rynofasho** · 03-04-2010, 01:23 PM

Originally Posted by Leith Ross

Hello Rynofasho,

I realize it has been awhile since you have posted. I have added code tags to your post as a courtesy this time, but please do so in the future. You should read the forum rules as there have been changes made to them.

Will do in the future. Thank you!

**Rynofasho** · 03-05-2010, 12:20 PM

Anybody know?

**JBeaucaire** · 03-05-2010, 12:25 PM

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**Rynofasho** · 03-05-2010, 05:40 PM

Here is the slightly changed sheet. Basically I want a worksheet change event to re-execute my macro every time the session high or session low changes (G2 and G3).

Also, for my Volume at price calc using an IF and SUMIF, is there a 'cleaner' way to do that in VBA? I don't really mind my solution as it keeps the sheet aesthetically pleasing and it populates within a second or two. Just curious mostly.

**Leith Ross** · 03-06-2010, 11:31 PM

Hello Rynofasho,

Since the data appears to be imported by a web query, the Worksheet_Change() event will not fire when the cells are updated. A different method would need to be used to call the macro. To do that would require your original workbook.

In the meantime, here is a VBA macro that fill in column "G" based on the high and low session values, sum all the entries in the "Trade" data, and the output those sums next to the corresponding value in column "H". This has been added to the attached workbook and can be run by clicking the button on the worksheet.

Please Login or Register  to view this content.

**Rynofasho** · 03-07-2010, 08:44 PM

Leith, thank you so much for the example. I appreciate it greatly. As far as my original workbook is concerned, there are no external weblinks. I am monitoring the session high and low via inhouse datafeed, and I can set up a function that will automatically update them if they change. The formula is not dynamic, so the actual results of the function (session high and low) are just numbers.

The data on the left can be completely disregarded as it is just tick data that was already there. Does this alter my ability to use the worksheet change event? I apologize in advance for being rather VBA illiterate. I ordered that VBA power programming book from Walkenbach but it hasn't arrived so I haven't really been able to dive into it.

Would you happen to have any examples of a worksheet change? And how do you "stack" the macros to where you can use one to call another? Is it necessary to create one as a module?