VBA: adding link anchor from the function

F

FlexMind

New Member
Joined
Apr 9, 2019
Messages
20
Hello!
Is it possible to add a link anchor from UDF?
I have a code:
Code: 
Function ActivateLinks(url As String)Dim linkAddr As String
linkAddr = "g5"
HyperLink url, linkAddr
End Function
Sub HyperLink(ByVal link As String, ByVal addr As String)
ActiveSheet.Hyperlinks.Add Anchor:=Range(addr), Address:=link
End Sub
Why does it not work?
Thank you in advance!
 
Sort by date Sort by votes
I found some solution, but not works for me:
Code: 
[COLOR=#333333][FONT='inherit']The solution is much more complicated because worksheet and range object can not be access through UDF.[/FONT][/COLOR][COLOR=#333333][FONT='inherit']Actually, the hyperlink is created by calculate event with following solution.[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']You can try following steps[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']1. Insert a class module and name it "clsSht" and add following code:[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000088][FONT=inherit]WithEvents[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Worksheet
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Rng [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Range
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#000000][FONT=inherit]

[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Private[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht_Calculate[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]On[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Error[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Next[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    Sht[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Hyperlinks[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Add Rng[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]End[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#333333][FONT='inherit']2. Insert a module and add following code:[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] clsSht
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Function[/FONT][/COLOR][COLOR=#000000][FONT=inherit] getLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]TS [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL   [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht     [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Worksheet
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Rng     [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Range
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'do evaluations to get myURL[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    myURL [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"http://www.google.com"[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'Add Hyperlink[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] clsSht
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Rng [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Application[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Caller
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Sht [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Application[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Caller[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Parent
    newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]myURL [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'Show Display text[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    getLink [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Click Me"[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]End[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Function[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']3. Try it[/FONT][/COLOR]
Maybe it is really impossible?
 
Upvote 0
I found some solution, but not works for me:
Code: 
The solution is much more complicated because worksheet and range object can not be access through UDF.Actually, the hyperlink is created by calculate event with following solution.
You can try following steps
Code: 
1. Insert a class module and name it "clsSht" and add following code:
Code: 
PublicWithEvents Sht As Worksheet
Public Rng As Range
Public myURL AsString

PrivateSub Sht_Calculate()
    OnErrorResumeNext
    Sht.Hyperlinks.Add Rng, myURL
EndSub
Code: 
2. Insert a module and add following code:
Code: 
Public newLink As clsSht
Function getLink(TS AsString)
    Dim myURL   AsString
    Dim Sht     As Worksheet
    Dim Rng     As Range
    'do evaluations to get myURL
    myURL ="http://www.google.com"
    'Add Hyperlink
    Set newLink =New clsSht
    Set newLink.Rng = Application.Caller
    Set newLink.Sht = Application.Caller.Parent
    newLink.myURL = myURL
    'Show Display text
    getLink ="Click Me"
EndFunction
Code: 
3. Try it
Maybe it is really impossible?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
17
Views
439
eli_m
E
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
200
Fluff
Fluff
P
  • Question Question
make an open link where macro asks everytime you click the button for the location & then remainig macro works as programmed
Replies
4
Views
94
Peter_SSs
Peter_SSs
E
  • Question Question
Copy Cell as Hyperlink to another cell
Replies
1
Views
356
eli_m
E
I
  • Question Question
Vba has stopped adding hyperlink to my customers name
Replies
3
Views
144
ipbr21054
I

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top