External link can't be broken

[newbie21]

I have a spreadsheet that I added (via Copy spreadsheet function on the tab name) to an existing workbook. There is an external link within that spreadsheet that I can't remove. I have clear out the Name Manager in the Formulas ribbon. I can see the external link in the Data>Edit Links dialog box but I can't Break Link nor Change Source. I have tried to download Kutools and that utility doesn't recognize any broken link even though excel itself tells me it's there.

The Source of the link is from the old worksheet that I used to copy the new spreadsheet over. The Type of the link is Worksheet, Update is A (?). Can someone please help?

 

[Ron Coderre]

Try checking the Data Validation cells.
I'm guessing there's at least one with a reference to the other workbook.

I've got a personal macro that I run for those situations. It adds a new worksheet and lists every DV cell in the source worksheet and the DV settings for those cells.

I'll share it here:

• ALT+F11…to open the Visual Basic Editor
• Right-click on your workbook's name in the VBA - Projects window
...Select: Insert Module
• Copy the below VBA code and paste it into that module

To run the code...
• ALT+F8...to open the macros window
...Select: ListWksDVCellSettings
...Click: Run

' ListWksDVCellSettings()
' o Inserts a new worksheet in the current workbook
' o Identifies every DV cell in the active sheet
' o On the new sheet, it lists:
'   ...Parent sheet name and cell address
'   ...DV Type
'   ...Formula1
'   ...Formula2
Sub ListWksDVCellSettings()
Dim wksCurr As Worksheet
Dim wksNew As Worksheet
Dim cCell As Range
Dim cStartCell As Range
Dim Ctr As Long
Dim DVType As Integer
Dim DVTypeDesc As String

Set wksCurr = ActiveSheet
Set wksNew = Worksheets.Add
Set cStartCell = wksNew.Range("A1")

On Error GoTo errTrap
Ctr = 1
wksCurr.Activate
wksCurr.Cells(1, 1).SpecialCells(xlCellTypeAllValidation).Select
With cStartCell
    .Value = "Worksheet: " & wksCurr.Name
    .Offset(RowOffset:=Ctr, ColumnOffset:=0).Value = "Cell Ref"
    .Offset(RowOffset:=Ctr, ColumnOffset:=1).Value = "DV Type"
    .Offset(RowOffset:=Ctr, ColumnOffset:=2).Value = "Formula1"
    .Offset(RowOffset:=Ctr, ColumnOffset:=3).Value = "Formula2"
    Ctr = Ctr + 1
    
    For Each cCell In Selection
        .Offset(RowOffset:=Ctr, ColumnOffset:=0).Value = cCell.Parent.Name & "!" & cCell.Address
        DVType = cCell.Validation.Type
        ' Determine validation type
        ' xlValidateInputOnly.....0
        ' xlValidateWholeNumber...1
        ' xlValidateDecimal.......2
        ' xlValidateList..........3
        ' xlValidateDate..........4
        ' xlValidateTime..........5
        ' xlValidateTextLength....6
        ' xlValidateCustom........7
        Select Case DVType
            Case 0:
                DVTypeDesc = "Input Only"
            Case 1:
                DVTypeDesc = "Whole Number"
            Case 2:
                DVTypeDesc = "Decimal"
            Case 3:
                DVTypeDesc = "List"
            Case 4:
                DVTypeDesc = "Date"
            Case 5:
                DVTypeDesc = "Time"
            Case 6:
                DVTypeDesc = "Text Length"
            Case 7:
                DVTypeDesc = "Custom"
        End Select
       
        .Offset(RowOffset:=Ctr, ColumnOffset:=1).Value = DVTypeDesc
        .Offset(RowOffset:=Ctr, ColumnOffset:=2).Value = "'" & cCell.Validation.Formula1
        .Offset(RowOffset:=Ctr, ColumnOffset:=3).Value = "'" & cCell.Validation.Formula2

        Ctr = Ctr + 1
    Next cCell
    wksNew.UsedRange.Columns.AutoFit
End With
errTrap:
If Err.Number <> 0 Then
    MsgBox "Could not complete operation"
End If
End Sub

Does that help?

 

[newbie21]

Ron,

Thank you for your response. I got a "Could not complete operation" error when running the macro. The IT person told me I have to rebuild the spreadsheet from scratch to get rid of this link since I can't find where it is being used.

 

[peejay]

I took this macro for a spin. It looks like this error message comes up if there are no Data Validations on the sheet being analysed.

Ron - I'd suggest changing the error message to reflect this, and maybe also remove the added sheet if there's nothing to report on it?

cheers PJ

 

[Michael M]

Rebuilding the workbook from scratch is a little extreme, and most likely unnecessary.....sounds like IT doesn't want to help resolve the actual problem.

IMHO, the FindLink tool from Bill Manville is the best around....Have a look here

FindLink


Also, have you looked in any Charts, Shapes, Pivot tables for the link ??
AND
Makes sure there are no protected / Hidden sheets that the link finder can't see !!