[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1364
  • Last Modified:

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.
0
doosydave
Asked:
doosydave
  • 2
  • 2
1 Solution
 
mvidasCommented:
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.

Cheers
Matt


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"
   Else
    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
  Next
 End If
End Sub

Open in new window

0
 
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.
0
 
doosydaveAuthor Commented:
Both practical and conceptual solution. great post.
0
 
mvidasCommented:
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now