Running code by clicking on a hyperlink cell

  • Thread starter Thread starter matpj
  • Start date Start date
M

matpj

Hi there,

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

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

what I need to do is create some code that will allow users to click o
a hyperlink titled "CPO Reports" in column C (there are many instance
of the same empty link) and then a message box will appear with
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 cod
when they are clicked, but I want to do this with the existin
hypelinks instead.

thank in advance,
Mat
 
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
 
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
 
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
 
Back
Top