Excel VBA: Creating Hyperlinks Type Mismatch

Question

I have created a function to create cross-reference hyperlinks in a workbook between worksheets. For some reason I am getting Error 13: Type mismatch error when it tries to create the hyperlink.

Here is the function for creating the hyperlinks:

Public Function SearchAndLink(txtTicketNum As String, shtFromSheet As Worksheet, rngFromCell As Range, txtFromText As String, shtToSheet As Worksheet, txtToText As String, numFromOff As Integer, numToOff As Integer) As Boolean

Dim rngToCell As Range

Set rngToCell = shtToSheet.Cells.Find(What:=txtTicketNum)

If Not rngToCell Is Nothing Then
    Sheets(shtFromSheet).Hyperlinks.Add anchor:=rngFromCell.Offset(0, numFromOff), Address:="", SubAddress:= _
        "'" & Sheets(shtToSheet).Name & "'!" & rngToCell.Address, TextToDisplay:=txtFromText

    Sheets(shtToSheet).Hyperlinks.Add anchor:=rngToCell.Offset(0, numToOff), Address:="", SubAddress:= _
        "'" & Sheets(shtFromSheet).Name & "'!" & rngFromCell.Address, TextToDisplay:=txtToText

    rngToCell.EntireRow.Interior.ColorIndex = -4142
    rngToCell.EntireRow.Font.Name = "Calibri"
    rngToCell.EntireRow.Font.Size = 11

    SearchAndLink = True
Else
    SearchAndLink = False
End If

End Function

And here is where I create the variables and call the function:

Public Sub CrossReference()
Dim strRORA As String
Dim rngslider As Range
Dim boolFound As Boolean

'Set variables to check RO's versus open tickets
Set rngslider = Sheets("Resource Orders").Range("A4")

Do While rngslider  "" 'continue while the name column has data

    ' set variables to check open tickets
    strRORA = UCase(rngslider.Offset(0, 10).Value)

    boolFound = SearchAndLink(strRORA, Sheets("Resource Orders"), rngslider, strRORA, Sheets("Open Tickets"), "RO", 10, 78)

I have tried entering the sheet and range manually to find the error, but I always get a type mismatch error. Am I using the variables in the function incorrectly? Any help would be most appreciated.

Answer 1

I figured out what the issue was. I was passing a worksheet object to the function, but the method for hyperlinks needs the index for a worksheet. I changed the function to pass the index of the worksheets I'm working with, and it creates the hyperlinks.

Here is the new function call:

 boolFound = SearchAndLink(strRORA, Sheets("Resource Orders").Index, rngslider, "Closed", Sheets("Closed Fire Tickets").Index, "RO", 11, 28)

Here is the new function:

Public Function SearchAndLink(txtTicketNum As String, numFromSheet As Integer, rngFromCell As Range, txtFromText As String, numToSheet As Integer, txtToText As String, numFromOff As Integer, numToOff As Integer) As Boolean

And here is the correct hyperlink code:

Sheets(numFromSheet).Hyperlinks.Add anchor:=rngFromCell.Offset(0, numFromOff), Address:="", _
        SubAddress:="'" & Sheets(numToSheet).Name & "'!" & rngToCell.Address, TextToDisplay:=txtFromText

Thanks!