Multiple Hyperlink/VBA Query

[OscartheDog]

Hi All

I was just wondering if anyone could help me with a Multiple Hyperlink/VBA query. I've tried multiple methods but none seem to be working.

I have a list of names where I want there to be a hyperlink option next to each individual name that will take the user to another page when clicked - it will also copy and paste the name they clicked into a certain cell (for example Sheet 2, Cell A1 - same sheet/cell for all individuals). I can do that using:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$G$4" Then
Call Macro1
End If
End Sub

with Marco1 copying the cell and pasting it into sheet 2.

Is there a way to use the above for a range of cells - otherwise this would have to be typed out 600 times+. This spreadsheet is something where names are added constantly so I just need to find a way to make this as automatic as possible without adding a new line to the above VBA.

Secondly, I need to find a way to add a hyperlink every time a new name is added to the list - of course it must refer to the cell it is so the above VBA would work.

It's a combination of these 2 things that I need/hope to get working.

I've already looked into the hyperlink function but this has issues - I'm not sure if you can run a macro from a click on a hyperlink function.

I can provide a sheet if needed or answer any questions but would really appreciate some help on this

Many Thanks

Oscar

 

[jasonb75]

Try changing the first line to something like

If Not Intersect(Target, Range("D10:G100")) Is Nothing Then

Adjust the Range as needed.

For simplicity, I would suggest using Double Click instead of Hyperlink.

 

[OscartheDog]

Thank you so much! I'll be honest - I didn't even know Double Click existed but you learn something new every day!

I've made a few little tweaks and it's all up and running - so thanks again!