Activate hyperlink based on cell value

[veggies27]

I am trying to automatically activate a hyperlink if a certain cell has a
value in it. Is there a way this is possible?

 

[Mike H]

hi,

It would have helped to know which cells and where the hyperlink was going
but try this. Right click your sheet tab and view code and paste this in.
Enter 99 in a1 and a hyperling is created in b1 to sheet 1 a1

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 99 Then
ActiveSheet.Hyperlinks.Add Anchor:=Range("B1"), Address:="", _
SubAddress:="Sheet1!A1", TextToDisplay:="MyText"
End If
End Sub

Mike

 

[Mike H]

Hi,

Similar solution

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 99 Then
Range("B1").Hyperlinks(1).Follow
End If
End Sub


Mike

 

[veggies27]

Mike,

One more try. For my spreadsheet, I converted the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E5").Value = X Then
Range("B5").Hyperlinks(1).Follow
End If
End Sub

However when I changed the "99" to an "X" it triggers the hyperlink when I
delete the X in the cell, not when I put the X in. Is that a text versus
numberic issue?

 

[Mike H]

Hi,

No problem we'll get there. using X in your code makes X a variable and not
the letter X. To make it the letter X put it in quotes like below. Now you
'may' encounter another problem because it becomes case sensitive so note how
i've included an UCASE statement so if you now enter x or X it works

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Range("E5").Value) = "X" Then
Range("B5").Hyperlinks(1).Follow
End If
End Sub

Mike

 

[veggies27]

Awesome! Thank you.

Hi,

No problem we'll get there. using X in your code makes X a variable and not
the letter X. To make it the letter X put it in quotes like below. Now you
'may' encounter another problem because it becomes case sensitive so note how
i've included an UCASE statement so if you now enter x or X it works

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Range("E5").Value) = "X" Then
Range("B5").Hyperlinks(1).Follow
End If
End Sub

Mike

 

[veggies27]

I've encountered another problem. When I use the code below, and type an "X"
in E5, it takes me right to the hyperlink location. However if the X stays in
E5 and I type anything anywhere else on the sheet, it activates the link too.
How can this code be modified to only activiate the hyperlink right after I
type the X in E5, and not all the time if an X exists in E5?