Running code by clicking on a hyperlink cell

[matpj]

Hi there,

I have a list of published reports in a worksheet, some of which have hyperlinks to their location in Clarity (browser based project management system) and some of which have empty hyperlinks (with a given title)

I also have some code that generates and sends an email from lotus notes.

what I need to do is create some code that will allow users to click on a hyperlink titled "CPO Reports" in column C (there are many instances of the same empty link) and then a message box will appear with a yes/no button.

If they click yes then the email code will run.

any suggestions?
I can create command buttons on each line that will activate the code when they are clicked, but I want to do this with the existing hypelinks instead.

thank in advance,
Matt

[papou]

Hello
Place this code below into the Worksheet module (right-click worksheet tab
and select view code and then paste)
NB: Please Amend accordingly:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.TextToDisplay = "CPO Reports" Then
Select Case MsgBox("run the mail macro", vbQuestion + vbYesNo, "Confirm")
Case vbYes: SendMail 'name of your macro to run
Case Else: Exit Sub
End Select
End If
End Sub

HTH
Cordially
Pascal

[matpj]

wow! thanks for that.

I made a mistake in my first post. The CPO Reports are not actually hyperlinks.
I'm writing this on behalf of a colleague, but he made them appear like hyperlinks, by underlining and colouring blue!!

Is there a way I can use your code for just activating the cell?
or can I create hyperlinks with no address or a null adress?

thanks so much,
Matt

[papou]

Matt
Yes you may consider creating hyperlinks to make my sample code work!
To do so you may only refer to each cell address for instance.

HTH
Cordially
Pascal