CODEWITHSUNDEEP
vbaexcelhyperlink
Gary's Student
Gary's Student

Reputation: 96771

Getting an Excel UDF to Return a Hyperlink

Currently to get a UDF to return a hyperlink I use something like:

Public Function HyperActive() As String
    HyperActive = "http://www.cnn.com"
End Function

and in a worksheet cell, I use:

=HYPERLINK(hyperactive(),"news")

to make a nice, "click-able" link.

I would like to have the UDF return a "click-able" link directly. I have tried:

Public Function HyperActive() As Hyperlink
    Dim h As Hyperlink
    h.Address = "http://www.cnn.com"
    Set HyperActive = h
End Function

just returns #VALUE! in the cell! How can get this to work??

Upvotes: 2

Views: 1945

Answers (1)

Keith Whatling
Keith Whatling

Reputation: 161

Hyperlinks like to be added to worksheets. You can add them to ranges two if you like. This function will add just that site, you can always flesh it out to take more args of you like.

It adds the hyperlink to the current selected cell. Though you could just as well change it to what ever you liked.

Public Function HyperActive(ByRef rng As Range)
With ActiveSheet.Hyperlinks
.Add Anchor:=rng, _
     Address:="http://www.cnn.com", _
     ScreenTip:="Click to go to the Cnn Website", _
     TextToDisplay:="CNN Website"
End With
End Function

Sub Calling_The_Hyper_link()
Call HyperActive(Selection)
End Sub

If you add Public Function HyperActive(byref sh as worksheet, ByRef rng As Range) you could control which sheet it goes in too.

Upvotes: 1

Related Questions