Adding Hyperlink to another tab in Excel VBA gives error 438

0
votes

I trying to add a hyperlink using this piece of VBA Code:

Sub macrotoc()

Dim sht As Worksheet
Dim targetsheet As Worksheet

 Set sht = ActiveWorkbook.Sheets("TOC")
 Set targetsheet = Worksheets("Monthly Enrollment")

 With sht

 .Hyperlinks.Add Anchor:=sht.Range("c5"), Address:="", SubAddress:=targetsheet & "!A1", ScreenTip:="Monthly Enrollment", TextToDisplay:="Monthly Enrollment" <the line where code is giving me error>

 End With

End Sub

This I pieced together with the help of several online forums. I want the hyperlink to appear in sheet TOC Cell C5, and point towards another worksheet "Monthly Enrollment" in the SAME workbook. Please note that I am going to be running this macro for several workbooks overtime so giving the location of workbook in the hyperlink.add line is not an option for me.

The code is now giving me Runtime Error 438: Object doesn't support this property or method on the Hyperlink.Add line

I have been trying variations of Activeworkbook or ThisWorkbook or Worksheets in the set sht= line but nothing has been helping.

1
excelvbahyperlinkruntime-error

1 Answers

1
votes

Try this instead - you were trying to concatenate a range with a string, better just to let it all be a string.

Sub macrotoc()

Dim sht As Worksheet
Dim targetsheet As String

 Set sht = ActiveWorkbook.Sheets("TOC")
 targetsheet = "Monthly Enrollment"

 With sht

 .Hyperlinks.Add Anchor:=.Range("C5"), Address:="", SubAddress:="'" & targetsheet & "'!A1", ScreenTip:="Monthly Enrollment", TextToDisplay:="Monthly Enrollment"

 End With

End Sub