Embed Hyperlinks

[Salza]

Hi everyone,

Is there a way to embed hyperlinks in either:-
a) dropdown lists
b) combo boxes
c) data validation boxes.

In my worksheet, I want to permit users to hyperlink out from these
functions based on their selection.
Please help. Thank you.

Regards,
Zainuddin Z

 

[Dave Peterson]

Not that I've ever seen.

Maybe you can use another cell:
=hyperlink("http://"&a1,"click me")
if you're using data|validation in A1
Or
if you're using a combobox from the control toolbox toolbar and A1 is the linked
cell.

or
=hyperlink("http://"&index(x1:x99,a1),"click me")
if you're using a dropdown from the Forms toolbar where A1 is the linked cell
and X1:x99 is the input range.

(And "http://" may be "mailto:" or "file:////" depending on what you're
hyperlinking to--and what values are returned in the linkedcell/index formula.)

 

[Salza]

Not that I've ever seen.

Maybe you can use another cell:
=hyperlink("http://"&a1,"click me")
if you're using data|validation in A1
Or
if you're using a combobox from the control toolbox toolbar and A1 is thelinked
cell.

or
=hyperlink("http://"&index(x1:x99,a1),"click me")
if you're using a dropdown from the Forms toolbar where A1 is the linked cell
and X1:x99 is the input range.

(And "http://" may be "mailto:" or "file:////" depending on what you're
hyperlinking to--and what values are returned in the linkedcell/index formula.)

Thanks, Dave. I will try it soon.
I appreciate your kind help.

 

[Chip Pearson]

You can do it with some programming. Drop a Combobox from the
Controls command bar onto Sheet1. Then, open VBA. Insert a new module
and paste in the following code:

Sub InitCBoxLinks()
Dim Arr(1 To 4, 1 To 2)
Sheet1.bInSetup = True
Application.EnableEvents = False
Arr(1, 1) = "Google"
Arr(1, 2) = "http://www.google.com"
Arr(2, 1) = "Yahoo"
Arr(2, 2) = "http://www.yahoo.com"
Arr(3, 1) = "Bing"
Arr(3, 2) = "http://www.bing.com"
Arr(4, 1) = "Pearson"
Arr(4, 2) = "http://www.cpearson.com"
With Sheet1.ComboBox1
.Clear
.ColumnCount = 2
.Width = 200
.ColumnWidths = "190;0"
.Style = fmStyleDropDownList
.List = Arr
.ListIndex = 0
End With
Application.EnableEvents = True
Sheet1.bInSetup = False
End Sub

Change the values assigned to Arr to meet your needs. The array has 4
pairs of values. You can increase this to as many element pairs as
you need. The first element of each pair is the text that will be
displayed in the combobox's dropdown. The second element of each pair
is the URL to which you want to navigate. The second element, the
URL, is not visible in the combobox. Only the first element is
visible.

Next, you need code in the Sheet1 module to handle the events of the
combobox. In the Sheet1 module, paste:

Public bInSetup As Boolean
Private Sub ComboBox1_Change()
Dim URL As String
If Me.bInSetup = True Then
Exit Sub
End If
With Me.ComboBox1
If .ListIndex < 0 Then
Exit Sub
End If

URL = .List(.ListIndex, 1)
End With
ThisWorkbook.FollowHyperlink URL
End Sub

Now, whenever the user changes the value of the combobox, Windows will
navigate IE to the web site associated with the selected value.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Salza]

You can do it with some programming.  Drop a Combobox from the
Controls command bar onto Sheet1.  Then, open VBA. Insert a new module
and paste in the following code:

Sub InitCBoxLinks()
    Dim Arr(1 To 4, 1 To 2)
    Sheet1.bInSetup = True
    Application.EnableEvents = False
    Arr(1, 1) = "Google"
    Arr(1, 2) = "http://www.google.com"
    Arr(2, 1) = "Yahoo"
    Arr(2, 2) = "http://www.yahoo.com"
    Arr(3, 1) = "Bing"
    Arr(3, 2) = "http://www.bing.com"
    Arr(4, 1) = "Pearson"
    Arr(4, 2) = "http://www.cpearson.com"
    With Sheet1.ComboBox1
        .Clear
        .ColumnCount = 2
        .Width = 200
        .ColumnWidths = "190;0"
        .Style = fmStyleDropDownList
        .List = Arr
        .ListIndex = 0
    End With
    Application.EnableEvents = True
    Sheet1.bInSetup = False
End Sub

Change the values assigned to Arr to meet your needs. The array has 4
pairs of values.  You can increase this to as many element pairs as
you need.  The first element of each pair is the text that will be
displayed in the combobox's dropdown. The second element of each pair
is the URL to which you want to navigate.  The second element, the
URL, is not visible in the combobox. Only the first element is
visible.

Next, you need code in the Sheet1 module to handle the events of the
combobox. In the Sheet1 module, paste:

Public bInSetup As Boolean
Private Sub ComboBox1_Change()
    Dim URL As String
    If Me.bInSetup = True Then
        Exit Sub
    End If
    With Me.ComboBox1
        If .ListIndex < 0 Then
            Exit Sub
        End If

        URL = .List(.ListIndex, 1)
    End With
    ThisWorkbook.FollowHyperlink URL
End Sub

Now, whenever the user changes the value of the combobox, Windows will
navigate IE to the web site associated with the selected value.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Hi everyone,

Is there a way to embed hyperlinks in either:-
a) dropdown lists
b) combo boxes
c) data validation boxes.

In my worksheet, I want to permit users to hyperlink out from these
functions based on their selection.
Please help. Thank you.

Regards,
Zainuddin Z

Dera Pearson,

Thank you so much for your reply,

The code looks very convincing and I am sure it works perfectly. But
due to my little knowledge in handling Excel 2007, I fail to execute
the code correctly. I guess I didnt use proper way to come out with
correct combo box. Do you mind giving step by step instructions on
how to get the combobox work.

Thank you, Pearson.

Regards,
Zainuddin