Hyperlink based on dropdown ?

[Iceshade]

Hi All,

I need some help please and I can't seem to find any guides that cover this, so apologies if this is very novice.

I have a drop down box and I am trying to get it to generate a hyperlink in the cell next to it, but assign a name (i.e. if url is www.google.com, give me a link that is titled "Link" - not show me the whole url. Example:

IF Cell A1 = "Google" THEN
Cell B1 = "www.google.com","Google Link" ELSE
IF Cell A1 = "Bing" THEN
Cell B1 = "www.bing.com","Bing Link" ELSE
Cell B1 = ""

Seems simple enough but I just can't get it to work. Tried =HYPERLINK function but it seems to work in reverse, i.e. dropdown has to have the whole URL in it.

Apologies if I am not making sense, I am trying to teach myself.

 

[kevin9999]

Try this in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A1"), Target) Is Nothing Then
    Dim ws As Worksheet: Set ws = ActiveSheet
        Select Case Target.Value
            Case Is = "Google": MyURL = "http://www.google.com"
            Case Is = "Bing": MyURL = "http://www.bing.com"
        End Select
        Target.Offset(, 1) = Target.Value & " Link"
        ws.Hyperlinks.Add Anchor:=ws.Range("B1"), Address:=MyURL
    End If
End Sub

 

[Iceshade]

Try this in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A1"), Target) Is Nothing Then
    Dim ws As Worksheet: Set ws = ActiveSheet
        Select Case Target.Value
            Case Is = "Google": MyURL = "http://www.google.com"
            Case Is = "Bing": MyURL = "http://www.bing.com"
        End Select
        Target.Offset(, 1) = Target.Value & " Link"
        ws.Hyperlinks.Add Anchor:=ws.Range("B1"), Address:=MyURL
    End If
End Sub

Thanks Kevin,

I've tried this but doesn't update anything. Can I just confirm:

Intersect(Range("A1") <---- this is my dropdown range
Anchor:=ws.Range("B1") <------ this where the hyperlink would be placed?

 

[kevin9999]

Thanks Kevin,

I've tried this but doesn't update anything. Can I just confirm:

Intersect(Range("A1") <---- this is my dropdown range
Anchor:=ws.Range("B1") <------ this where the hyperlink would be placed?

A1 is the cell that changes according to your dropdown choice. B1 is the cell where the link is inserted. Make sure the code goes in the worksheet code area: right-click on the tab name, select view code - the code goes there. See if the below result is what you're after (tested & works for me).

hyperlink.xlsb
	A	B	C	D
1	Google	Google Link		Google
2				Bing
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$D$1:$D$2

 

[Iceshade]

A1 is the cell that changes according to your dropdown choice. B1 is the cell where the link is inserted. Make sure the code goes in the worksheet code area: right-click on the tab name, select view code - the code goes there. See if the below result is what you're after (tested & works for me).

hyperlink.xlsb
	A	B	C	D
1	Google	Google Link		Google
2				Bing
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$D$1:$D$2

Really appreciate your responses Kevin, thank you.
So I did copy into worksheet but it won't show me anything. Saved and reopened the workbook which didn't help either. This is how I amended below, it may be because in my example I said A1 (for example purpose) but the value is in B12 - link should be C12.

 

[kevin9999]

I changed the locations to your actual requirement, amended the code, saved & tested it & it still works fine at my end...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("B12"), Target) Is Nothing Then
    Dim ws As Worksheet: Set ws = ActiveSheet
        Select Case Target.Value
            Case Is = "Google": MyURL = "http://www.google.com"
            Case Is = "Bing": MyURL = "http://www.bing.com"
        End Select
        Target.Offset(, 1) = Target.Value & " Link"
        ws.Hyperlinks.Add Anchor:=ws.Range("C12"), Address:=MyURL
    End If
End Sub

hyperlink.xlsb
	B	C	D	E
12	Bing	Bing Link		Google
13				Bing
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B12	List	=$E$12:$E$13

I'm not sure there's much else I can suggest - sorry.

 

[Iceshade]

I changed the locations to your actual requirement, amended the code, saved & tested it & it still works fine at my end...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("B12"), Target) Is Nothing Then
    Dim ws As Worksheet: Set ws = ActiveSheet
        Select Case Target.Value
            Case Is = "Google": MyURL = "http://www.google.com"
            Case Is = "Bing": MyURL = "http://www.bing.com"
        End Select
        Target.Offset(, 1) = Target.Value & " Link"
        ws.Hyperlinks.Add Anchor:=ws.Range("C12"), Address:=MyURL
    End If
End Sub

hyperlink.xlsb
	B	C	D	E
12	Bing	Bing Link		Google
13				Bing
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B12	List	=$E$12:$E$13

I'm not sure there's much else I can suggest - sorry.

Thanks Kevin,

I'll keep at it - very strange as I can see I've done exactly as you have.

Appreciated, gives me something to work with at least.

 

[kevin9999]

Glad I could help, just wish I could have gotten it to work for you. Strange indeed...