Firing the FollowHyperlink Event and passing variables

In column E of my spreadsheet, I have programmed some logic in to the cell that compares 2 values, and chooses which hyperlink is available to the user. If the condition is true, the hyperlink goes to a folder directory where there is no additional programming needed. If it is false, the hyperlink opens an Outlook Template where the user can send me an email.

I was hoping to pass the value of column A and B as variables and output it with the instance of the Outlook email template.

I've fidgetted around with the FollowHyperlink event with little success. I can't even get the event to fire. Something that concerns me is that maybe the FollowHyperlink is unavailable when the Hyperlink() function is nested or maybe it has something to do with 2 hyperlinks within 1 cell.

Here is my formula in E:

=IF(C2>=D2, HYPERLINK("j:\forms\election\"&TEXT(TRUNC(TRIM(A2)/1000)*1000,"0000")&"-"&TEXT(TRUNC(TRIM(A2)/1000)*1000+999,"0000")&"/"&LEFT(TRIM(TEXT(A2,"0000")),2)&"00s/"&TEXT(TRIM($A2),"0000")&"/election","click here to find the updated form yourself!"), HYPERLINK("J:\Forms\Election\Form Database\request.oft","click to request updated forms!"))

Please help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Dave,

FollowHyperlink is designed to catch hyperlink objects, not formulas. An object is what you get when you go to Insert -> Hyperlink.

We can use VBA to mimic your IF formula using the worksheet_change event, but that still doesn't allow us to pass variables to the email template. Note that the FollowHyperlink event is triggered after the hyperlink is actually followed.

The attached code snippet will basically function the way your IF formula is doing now, but using Hyperlink objects.  It uses _change to see if columns A, C, or D were changed, and inserts a hyperlink in that row's E.

One possibility for you to send the values is to not actually open the template, but put a "#" in for the hyperlink address:
    'HypAddress = "J:\Forms\Election\Form Database\request.oft"
    HypAddress = "#"

Then use FollowHyperlink to catch that:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
 If Target.Name = "click to request updated forms!" Then
  MsgBox "You called this link from " & Target.Range.Address(0, 0) & vbCrLf & _
   "Column A: " & Target.Range.EntireRow.Cells(, "A").Value & vbCrLf & _
   "Column B: " & Target.Range.EntireRow.Cells(, "B").Value
  'using outlook object in vba, open template and add your desired values that way
 End If
End Sub

I'm not actually providing the code to control outlook, but this is one way to do it.


Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Intersect(Union(Columns("A"), Columns("C:D")), Rows("2:" & Rows.Count))) Is Nothing Then
  Dim CLL As Range, HypText As String, HypAddress
  For Each CLL In Target.EntireRow.Rows
   CLL.Cells(, "E").Hyperlinks.Delete
   If CLL.Cells(, "C") > CLL.Cells(, "D") Then
    HypText = "click here to find the updated form yourself!"
    HypAddress = "j:\forms\election\" & _
     Format(CInt(CInt(CLL.Cells(, "A").Value) / 1000) * 1000, "0000") & "-" & _
     Format(CInt(CInt(CLL.Cells(, "A").Value) / 1000) * 1000 + 999, "0000") & "\" & _
     Left(Format(CLL.Cells(, "A").Value, "0000"), 2) & "00s\" & _
     Format(CLL.Cells(, "A").Value, "0000") & "\election"
    HypText = "click to request updated forms!"
    HypAddress = "J:\Forms\Election\Form Database\request.oft"
   End If
   Hyperlinks.Add Anchor:=CLL.Cells(, "E"), Address:=HypAddress, TextToDisplay:=HypText
 End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doosydaveAuthor Commented:
Thanks, Matt.

This was the guidance I was looking for. For some common sensical reason, I thought I could fire the FollowHyperlink event simply by following a hyperlink using any excel object.

But then I was thinking how could someone control the code to execute if there were more than 1 hyperlink on the page.

Maybe they need a FollowAnyHyperlink event. Thaty would clear things up.
doosydaveAuthor Commented:
Both practical and conceptual solution. great post.
Common sense? Why would you think that FollowHyperlink catches a formula called "hyperlink"?? ;) To be honest, I never actually tried using it to catch the formula, I would have assume it would work as well.

If you want to give the outlook portion a try, let me know if you get stuck anywhere. I didn't want to put in that time if you weren't open to it, but I'll help you if you'd like.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.