6

I am trying to automatically add hyperlink and increment each time a new hyperlink is added.

However, I am getting in my vba script of hyperlink :

"invalid procedure call or argument error"

I am getting error at end of code from "linkedSheet.Hyperlinks" to "targetSheet.Name"

Any help would be great!

Dim targetSheet As Worksheet
Dim targetRange As Range
Dim linkedSheet As Worksheet
Dim linkRange As Range
Dim TargetRow As Long

'set variable to the sheet the hyperlink will link to
Set targetSheet = ThisWorkbook.Sheets(ActiveSheet.Name)

' specify the range on the summary sheet to link to
Set targetRange = targetSheet.Range("A1:Z100")

' set variable to sheet that will have the hyperlink
Set linkedSheet = ThisWorkbook.Sheets("Management")

For i = 6 To 1006
  Set Value = linkedSheet.Cells.Item(i, "C")
  If Len(Value) = 0 Then
  TargetRow = i
    Exit For
  End If

Next i

targetLocation = "C" & TargetRow

' specify where on that sheet we'll create the hyperlink
Set linkRange = linkedSheet.Range(targetLocation)

' create the hypperlink on the copied sheet pointing
' back to the summary sheet

  linkedSheet.Hyperlinks.Add Anchor:=linkRange, Address:="", SubAddress:= _
  "'" & targetSheet.Name & "'!" & targetRange.Address, _
  TextToDisplay:=targetSheet.Name
Community
  • 1
  • 1
vinit
  • 85
  • 9
  • I just checked how I used it in a previous work, and I didn't use the `SubAddress` but the `Address`. But my need was a bit different, I was linking a cell to another file, so as it was an external link, I'm not sure it is the same for you. – R3uK Aug 11 '15 at 14:06
  • No I tried using just the Address but it doesn't work, it needs the SubAddress – vinit Aug 11 '15 at 14:13
  • 2
    I can't seem to replicate your error... Your code works just fine for me... Maybe try : `targetRange.Address(True, True)` instead of `targetRange.Address` And if it is not working, display your variable in a `MsgBox` to check if everything seems fine... And if it is... I don't know... – R3uK Aug 11 '15 at 15:03
  • I tried doing targetRange.Address(True, True) but it doesn't work. I don't know if this helps or not but this code is in a table which is synchronized with sharepoint – vinit Aug 11 '15 at 15:13
  • Synchronized how? And you mean the management sheet is the reflect of Sharepoint data? Not sure if it's help... I guess your workbook and everything isn't in protected mode... I honestly have no clue what to do next as this work smoothly for me! – R3uK Aug 11 '15 at 15:30
  • 1
    What error are you getting on that last line? – DiegoAndresJAY Aug 11 '15 at 15:49
  • on the last line of code its saying Run-time error '5': Invalid procedure call and argument – vinit Aug 11 '15 at 17:03
  • 1
    @R3uK yes the management sheet is the reflect of sharepoint.. this code works on regular excel sheet but the same code doesn't work on the synchronized sheet – vinit Aug 11 '15 at 17:06
  • @vinit : ok so check if there is any kind of protection on that sheet. And how is synchronized with SharePoint? – R3uK Aug 12 '15 at 06:57
  • @R3uK : There is no protection on the sheet. And the code I m running is for table which is synchronized with SharePoint, so that if I make any changes in either the sheet or SharePoint it also makes those same changes in the table. – vinit Aug 12 '15 at 13:47

0 Answers0