2

Is there away to make the "=Hyperlink(Path,Name)" formula, within Excel, trigger a macro when clicked?

I attempted to use the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If  WithinRange(Target.Range.Address ,"CustomerNameList")=True Then  
      'run code 
      Exit Sub  
End If  
End Sub

This works perfectly for traditional hyperlinks but does not appear to work with the hyperlink formula. Ultimately, I would like a way to modify the above code to make it work with the hyperlink function.

Additionally, this workbook is not static. New rows are added from the top, shift down. Using the hyperlink formula has made my other processes much simpler and I would rather not have to write code that hardcodes a hyperlink each time an item is added or a name is changed. I also would prefer to not use an Onclick event with the cells within that range as the user may just want to modify the customer name and not follow the hyperlink.

I realize this is a lot of conditions and this just may not be possible but any suggestions would be appreciated.

John W
  • 191
  • 2
  • 6
  • 14
  • This previous question might provide a helpful push in the right direction: http://stackoverflow.com/questions/14422003/vba-to-open-excel-hyperlink-does-not-work-when-hyperlink-generated-with-a-formul – Gilligan Oct 28 '15 at 15:10
  • This one might also be helpful: http://stackoverflow.com/questions/14574281/excel-thisaddin-vb-why-is-application-sheetfollowhyperlink-not-called-for-hyp – Gilligan Oct 28 '15 at 15:11
  • http://stackoverflow.com/questions/33114093/excel-hyperlink-to-nothing/33115239#33115239 – Tim Williams Oct 28 '15 at 16:23
  • @Tim Williams & EntryLevel. Thank you both for the quick responses. I ended up following ExcelHero's technique from Tim's link. Referencing a user created function to run my code is something I hadn't considered and I think this may work better than I had hoped. Just as a side note, none of the searches for this problem returned the links each of you posted so hopefully the phrasing of my question will direct someone else in the right direction. – John W Oct 28 '15 at 18:18
  • Also: http://stackoverflow.com/questions/32660492/running-vba-from-a-hyperlink/32665442#32665442 (which preceded Tim's link) – lori_m Oct 28 '15 at 18:43
  • @lori_m - that was the one I was looking for when I posted the other link ;-) – Tim Williams Oct 28 '15 at 18:52
  • @lori_m Thanks Lori, I appreciate the extra info you included in your post. Tim, I am glad I am not the only one having trouble finding relevant posts. – John W Oct 28 '15 at 19:03
  • @lori_m My original hyperlink opened another workbook. However when I tried to add code to open a workbook in the function nothing happened. I am able to easily reference the path but the following code does nothing: `Set x = Application.Workbooks.Open(ChecklistPath_str)`. Furthermore, I found that I can't insert breaks to see the code in action, yet I know it's working because the msgboxes appear. – John W Oct 29 '15 at 20:02

0 Answers0