HyperLink problem

  • Thread starter Thread starter luda
  • Start date Start date
L

luda

Hi,
I need to VBA the creation (and subsequent update) of
a hyperlink to an external file in a shared workbook. The
VBA gives me a runtime error. Any ideas on how to go
around the apparent restriction?
Note: The sharing cannot be turned off.

If you could copy your response to my e-mail
(e-mail address removed), I will appreciate it greatly.
Thanks in advance,
Luda
 
'shname : name of the sheet, range string

Call DynamicHyperLinks(ShName, "C156:C230")

'Here is the function for hyperlinks
Sub DynamicHyperLinks(ShtName As String, Rng As String)
Dim C1
Dim C
Dim sh As Worksheet
Dim LinkStr

For Each C1 In Sheets(ShtName).Range(Rng)

LinkStr = Split(C1, ".")
C1.Value = LinkStr(0) 'checking file name & sheet
name then creates link
For Each sh In ActiveWorkbook.Sheets

If sh.Name = LinkStr(0) Then

Set C = Sheets(ShtName).Range(Rng).Find
(What:=sh.Name, LookAt:=xlWhole)
ActiveSheet.HyperLinks.Add Anchor:=C,
Address:="", SubAddress:="'" & sh.Name & "'!A1",
ScreenTip:="Click On Links"
End If

Next

Next

Set sh = Nothing
End Sub
 
Back
Top