Search code examples
vbaexcelexcel-2013

Set Hyperlink Through Code

I am trying to set-up VBA syntax to create hyperlinks in a few cells. This is what I have it down to, but I get a debug error of 'invalid procedure call or argument'

This is my syntax that produces the err

Public Sub AddHyperlinks()
Dim ws As Worksheet, lastrow As Long, i As Long
Set ws = ActiveSheet
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow
  ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 3), Address:="", SubAddress:="'" & ws.Cells(i, 2).Value & "'!A1", TextToDisplay:=ws.Cells(i, 3)
Next i
End Sub

Sample Workbook
Sample Workbook

Solution

  • The TextToDisplay does need to be a string. Using CStr (convert to string) will take care of that.

    TextToDisplay:=CStr(ws.cells(i, 3))

    After making that change, this tests successfully with your sample workbook.