Conditional Hyperlink?

  • Thread starter Thread starter mas_it_2000
  • Start date Start date
M

mas_it_2000

Hi everyone,

I want to do thing like this: If A1=1, then hyperlink B2 and KP (where
kp is a Name Box that exist somewhere in the workbook), otherwise leave
B2 blank.

How can I do so?

Regards,
Mike
 
Hi

AFAIK you'll need to use code to achive this, e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo err_Handler
If Target.Address = "$A$1" And Target.Value = 1 Then
Range("B1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="KP", TextToDisplay:="KP"
Else
Range("B1").Value = ""
End If
err_Handler:
Application.EnableEvents = True
End Sub
----

to use the code, right mouse click on the sheet you want to run it against,
choose view code
and copy & paste the above in - if you have any red lines, go to the end of
the first one and press delete - this should overcome any wordwrap problems.

Hope this helps
Cheers
JuileD
 
Julie,

Imagin I have a tabel of such links, how would this become possible; if
still? Beside that table length could vary!

Regards,
Mike
 
Hi Mike

create a dynamic range name (e.g. MyLinks) consisting of one column with the
values in it that match to the hyperlink names
(to create a dynamic range name check out
http://www.contextures.com/xlNames01.html#Dynamic)

in the column next to this one, put in the range names that you want each
value to match to
e.g.
G1:G5 has 1,2,3,4,5 - this is the one you've names as a dynamic range
H1:H5 has KP,AA,AB,AC,AD

now change the code that i gave you to:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo err_Handler
If Target.Address = "$A$1" Then
For Each c In Range("MyLinks")
If Target.Value = c.Value Then
Target.Offset(0, 1).Hyperlinks.Add Anchor:=Target.Offset(0,
1), Address:="", _
SubAddress:=c.Offset(0, 1).Value,
TextToDisplay:=c.Offset(0, 1).Value
End If
Next
End If

err_Handler:
Application.EnableEvents = True
End Sub

---
where "MyLinks" is the name of your dynamic range.

Hope this helps
Cheers
JulieD
 
Back
Top