Show Links to Current Active Sheet

[willastrowalker]

Hello, fellow excel nerds.

I am trying to get a macro to work (again) that is designed to find all sheets in a workbook that have formulas that are linking to the current active sheet. I found this solution online and it actually worked when I first got it. I created an XLAM to have it on hand, but now when I run it, the output is reversed.

Instead of a message box that showing other tabs that link to the current sheet, it now shows the sheets that formulas on the current tab are linking to.

The bizarre thing is that it did work when I first used it, but now it doesn't.. Tried recopying etc. but is not functioning. Any ideas?

From this website:

https://www.datawright.com.au/excel_resources/show_which_worksheets_link_to_the_current_sheet.htm

Sub ShowLinks()
''==============================================
''Find formulas that reference other sheets, and
''display a list of referenced sheets
''==============================================
    Dim Rng As Range, _
        c As Range
    Dim dic As Object, _
        dic2 As Object
    Dim x, y, z
    Dim j As Long, _
        k As Long, _
        m As Long
    Dim Sht As Worksheet
    Dim strSheets As String
    
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic2 = CreateObject("Scripting.Dictionary")
    Set Rng = Cells.SpecialCells(xlCellTypeFormulas)
    j = 0
    For Each c In Rng
        If InStr(1, c.Formula, "!") > 0 Then 'references another sheet
        'load all unique strings into a Dictionary object
            x = Split(c.Formula, "!")
            If Not dic.exists(x(0)) Then
                j = j + 1
                dic.Add x(0), j
            End If
        End If
    Next c
    If j=0 Then 'no formulas with links
        MsgBox "This sheet is not linked to other sheets", vbInformation
        GoTo ExitHere
    End If
    y = dic.keys
    'Now we have a list of unique strings containing sheet names
    'referenced from this sheet. Next step is to list just the sheet names.
    m = 0
    For k = LBound(y) To UBound(y)
        For Each Sht In ActiveWorkbook.Worksheets
            If InStr(1, y(k), Sht.Name) > 1 Then
                If Not dic2.exists(Sht.Name) Then
                    m = m + 1
                    dic2.Add Sht.Name, m
                End If
                Exit For
            End If
        Next Sht
    Next k
    strSheets = Join(dic2.keys, vbCrLf)
    MsgBox strSheets

ExitHere:
    Set dic2 = Nothing
    Set dic = Nothing
    Set Rng = Nothing [COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]

 

[ask2tsp]

if you want it the other way around you coul use

Sub ShowLinksFromOtherSheets()
''==============================================
''Find formulas in other sheets that reference this sheet
''and display a list of those sheets
''==============================================
    Dim sht As Worksheet, c As Range, sheetlist As String
        
    For Each sht In ThisWorkbook.Worksheets
        If sht.Name <> ActiveSheet.Name Then
            For Each c In sht.UsedRange
                If c.HasFormula Then
                    If InStr(1, c.Formula, ActiveSheet.Name & "!") Then
                        sheetlist = sheetlist & Chr(10) & sht.Name
                        Exit For
                    End If
                End If
            Next c
        End If
    Next sht
    MsgBox "The following sheet(s) refer to this sheet:" & sheetlist, vbOKOnly
End Sub