Selecting a hyperlink once condition met

[millerjj22]

Hello,

I have a spreadsheet which is pulling in live data. Once a condition is met and a cell in the sheet returns a 1, I want to select a hyperlink to send users an alert SMS. I was trying to use something like I have put below. It works when I manually step through the formula and force the value in the reference cell to be 1 but I am unable to make this run continuously / monitor in the background. Is something missing or is there an option I need to be using to have excel be constantly monitoring ?

Please Login or Register  to view this content.

As a further requirement, ideally I would be able to stop the macro from looping and sending repeated messages on the same item. - Essentially, once a value of 1 has been returned and the hyperlink cell followed, i would want an "override" cell to become active which the user would then clear once they have processed the alert.

Many thanks

[Sintek]

Hi millerjj22
Is only C44 going to be populated with the no 1 and only A44 going to have the hyperlink

[millerjj22]

Yes..... To be totally honest, I was hoping to apply this to a list of cells (c44:c54) or similar but I thought I would cross one bridge at a time !

Many thanks

[Sintek]

Try this

Please Login or Register  to view this content.

[millerjj22]

Ok, thanks - that works on a list now. I still need to manually "play" the macro or step through it, how do I have it monitoring / always running so that as soon as the value in c44 changes to a 1, the macro picks it up and executes the hyperlink using the above code ?

Thanks a lot

[Sintek]

Put this code in the sheet module

Please Login or Register  to view this content.

[millerjj22]

Hello,

Sorry, perhaps I misunderstood but the sheet is still not "monitoring". As you can see from attached, if anything in c44:c54 is changed, a website is not launched.

Many thanks

[Sintek]

Here you go:
Code to be placed in the sheet object "sheet1"

[millerjj22]

Hello again,

Thank you very much sintek. That works in isolation. When I try to copy and paste this code into a new module in a larger datasheet, I am back to nothing happening. Is there any setting that I need to consider to allow this ongoing monitoring or do I need to specify the sheet name ("summary") in the vba code ?

Much appreciated, it was great to have the websites launch immediately when I entered the 1!

[humdingaling]

did you put the code under the correct worksheet?

Please Login or Register  to view this content.

did you expand this to cover your expected area?

[Sintek]

You need to put the code within the sheet object....So if the worksheet which contains the website links is called Sheets("Summary")
The code goes into the Sheets("Summary") object..right click on sheet, click view code and paste code in

[millerjj22]

Yes I did. In a fresh module in the right worksheet!

I was hoping to just drop this extra piece of code into the large workbook but something is causing it not to work.

Thanks

[Sintek]

Nope not a module.......in the worksheet itself

[millerjj22]

AHA !

Sorry, user error on my part.... that is awesome, thanks.

If I may push my luck a little, is there an easy way to achieve the second part of my query? - Every time the sheet gets refreshed with data and then recalculates, the code loops and opens the website again.

"As a further requirement, ideally I would be able to stop the macro from looping and sending repeated messages on the same item. - Essentially, once a value of 1 has been returned and the hyperlink cell followed, i would want an "override" cell to become active which the user would then clear once they have processed the alert."

Thanks so much

[Sintek]

Please Login or Register  to view this content.

Or are you wanting a message to come up that says link alert sent and then have another cell's value filled with a flag od sorts...Don't know how your info gets populated in Column C

[millerjj22]

Ah, ok - as simple as that ! - I was thinking of much more convoluted ways of achieving that.... I can just add a further macro which replaces the formula in the cell when the user has actioned the alert.

Many thanks !

[Sintek]

Pleasure