Macro not working when called by IF formula

Many thanks for the example code. I can see from looking at it how it "should" work but the live monitoring feature does not work when I use this in isolation. - I tried to just set up from a blank workbook to test what was sent as well as to be able to send the example here if it failed.

So as you will see, in range X4:X21, there are simple IF tests on cells in columns T&U. As soon as the value in either changes to make the formula in column X true and therefore return a value of 1, I want to activate the hyperlink in column W (for the same row as the alert / value was generated). When that hyperlink has been activated, I want to then put an override / marker in AA column and the same row for which the alert is generated to serve as a value used in a further IF statement to then check if the hyperlink has been activated.

I hope that this makes a little more sense. - My primary issue with the new code which was sent is that it does not seem to monitor / be running in the background. - The previous code picked up whenever the value in column x changed to a 1 and launched the hyperlink.

Many thanks for the help

JM

Hello,

Many thanks for that. I have the code working in the simple sheet now.

I am now stuck in a slightly different way. When I paste that code into the sheet which I have already with data links etc, after a few minutes, I end up with a run time error 13 = type mismatch. When I go to debug, the section of code highlighted as the issue is as below. I don't see where the issue is coming and am unclear as to how to trouble shoot it. When this error occurs, the sheet stops updating from the live datasource (Bloomberg) until I delete the code from the worksheet code module and reset the sheet.

Please Login or Register  to view this content.

Any ideas or pointers for how to fix this or further trouble shoot would be really useful.

Thanks

JM

Thanks very much for the alternate way. There is good news and bad news.....

Good is that it lasts longer before going to error (on the same line of code as before)

Bad is that it still is doing the same thing.

I REALLY appreciate the help but am not sure what additional information may be of use in trouble shooting this. - It is a 14mb file as it pulls and stores / references quite a lot of data.

If there is another way to tackle the issue, then I can try that but I cant help but wonder if it could be some sort of setting in the sheet or the fact that it keeps getting refreshed from the external data source ?

Many thanks

JM

Hello Bernie,

That seems to have done it ! - Just having the code split up into separate chunks has done the trick.

Thanks so much !

JM

Hi there,

I seem to now be getting the error run time error 9. subscript out of range.....

The piece of code which is highlighted when it breaks out is

Please Login or Register  to view this content.

I am re-stumped !

Sorry but any ideas as to why this would be breaking out ? - The links in the column are clickable and work fine...

Thanks

Hello,

I attach a slimmed down copy of the workbook which is causing me issues. I can construct a web address in column AE and it is clickable but as soon as I change the value in column W to reference the constructed website address, the VBA breaks out with runtime error 9. The code below works fine when the cell value in the referenced range is just text but when I refer to another cell (even using command =HYPERLINK(CELL REF), it breaks the VBA. The columns W, AA & AE are the main ones to look at.

The section in bold below is where the error occurs, I don't see why changing the cell reference / contents should make it break like this. Does anyone have any ideas as to why this might be ?

Thanks a lot

JM

Please Login or Register  to view this content.

Thanks for the alternate suggestion.

I have tried both just entering the text as well as using the =HYPERLINK() command and neither work. The error I now get is run time error 438. Object doesn't support this property or method.

Sorry about this but are there any other possible ways to achieve this. - It just seems so odd that I can create a web address manually and click it myself but as soon as I use the reference to another cell / monitoring that it breaks out !

Many thanks