Active sheet has linked cells

BirdWatchers
BirdWatchers used Ask the Experts™
on
If activeworkbook.linkedsources determines if the workbook has links, what about tab specific?  Activesheet.linksources doesn't work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
You can use the following add-in..

http://www.manville.org.uk/software/findlink.htm

This will tell you all your links in your excel workbook and which range or which sheet they exists..

Saurabh...

Author

Commented:
with hundreds of users I am not allowed to use add-ins. Must be in VBA.
Top Expert 2015

Commented:
So what i understand you want to list all the links which is their on the workbook??

Saurabh..
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Sub ListLinks()

If SheetExists("Link List") = True Then
 Application.DisplayAlerts = False
 Sheets("Link List").Delete
 Application.DisplayAlerts = True
End If

ListExternalFormulaReferences

End Sub

Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then
    SheetExists = True
    End If

End Function

Sub ListExternalFormulaReferences()
    Dim ws As Worksheet, TargetWS As Worksheet, SourceWB As Workbook
    If ActiveWorkbook Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    With ActiveWorkbook
        On Error Resume Next
        Set TargetWS = .Worksheets.Add(Before:=.Worksheets(1))
        If TargetWS Is Nothing Then ' the workbook is protected
            Set SourceWB = ActiveWorkbook
            Set TargetWS = Workbooks.Add.Worksheets(1)
            SourceWB.Activate
            Set SourceWB = Nothing
        End If
        With TargetWS
            .Range("A1").Formula = "Sequence"
            .Range("B1").Formula = "Cell"
            .Range("C1").Formula = "Formula"
            .Range("D1").Formula = "Tab"
            .Range("A1:C1").Font.Bold = True
        End With
        For Each ws In .Worksheets
            If Not ws Is TargetWS Then
                ListLinksInWS ws, TargetWS
            End If
        Next ws
        Set ws = Nothing
    End With
    With TargetWS
        .Parent.Activate
        .Activate
        .Columns("A:C").AutoFit
        On Error Resume Next
        .Name = "Link List"
        On Error GoTo 0
    End With
    Set TargetWS = Nothing
    Application.ScreenUpdating = True
End Sub

Private Sub ListLinksInWS(ws As Worksheet, TargetWS As Worksheet)
Dim cl As Range, cFormula As String, tRow As Long
    If ws Is Nothing Then Exit Sub
    If TargetWS Is Nothing Then Exit Sub
    For Each cl In ws.UsedRange
        cFormula = cl.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 1) = "=" Then
                If InStr(cFormula, "[") > 1 Then
                    With TargetWS
                        tRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & tRow).Formula = tRow - 1
                        .Range("B" & tRow).Formula = ws.Name & "!" & _
                            cl.Address(False, False, xlA1)
                        .Range("C" & tRow).Formula = "'" & cFormula
                        .Range("D" & tRow).Formula = ws.Name
                    End With
                End If
            End If
        End If
    Next cl
    Set cl = Nothing
    Application.StatusBar = False
End Sub
Top Expert 2015

Commented:
Do you need help on the code?? or what you are looking for by posting the code here?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial