2

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??

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I think your out of luck; A UDF cannot modify the state of a cell/sheet, just its value. (You cant even change colours) – Alex K. Jul 16 '15 at 15:15
  • You'd have to use Windows timers I think. What's wrong with the `HYPERLINK` function? – Rory Jul 16 '15 at 15:23
  • I agree with @AlexK - I don't think this is possible, or at least it hasn't been for quite a while. I even tried having the function call a sub to do it, no luck. – Raystafarian Jul 17 '15 at 13:44
  • 1
    Have you tried this trick yet? http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – Byron Wall Jul 17 '15 at 14:38
  • @ByronWall Thanks.....I tried it....I discovered that a *UDF* **can** return a Hyperlink Object to a sub, but not to a worksheet cell....so I am using a sub to insert the hyperlink to a cell. – Gary's Student Jul 17 '15 at 14:44
  • Post your code when you finish. I'm curious. – Byron Wall Jul 17 '15 at 14:45
  • @ByronWall I will later to day....once I do some clean-up! – Gary's Student Jul 17 '15 at 14:46

1 Answers1

1

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.

Keith Whatling
  • 161
  • 6