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 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
<[email protected]> wrote in message
news:[email protected]...
> 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
>
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
"Mike" <[email protected]> wrote in message
news:[email protected]...
> Julie,
>
> Imagin I have a tabel of such links, how would this become possible; if
> still? Beside that table length could vary!
>
> Regards,
> Mike
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks