VBA Code - Trace Precedents across worksheets by making Go To box pop up

[CSSouthern]

Hi,

I know this VBA code is out there, but seemingly haven't found the right formula. Can someone help me figure out how to trace precedents across worksheets using a macro that pops open the GO TO box after using the shortcut key.

Thanks!

 

[Dan_W]

Hi - it's a one-liner. You just need to put it in a sub:

Application.CommandBars.ExecuteMso "GoTo"

If you need to know other shortcuts, built-in commands are called IdMSO. Hope that helps.

 

[CSSouthern]

Hi - it's a one-liner. You just need to put it in a sub:

Application.CommandBars.ExecuteMso "GoTo"

If you need to know other shortcuts, built-in commands are called IdMSO. Hope that helps.

Thank you Thank you Thank you! I have been looking for this for a few days! Really Appreciated

 

[CSSouthern]

Actually, this only goes to cells in that sheet. Do you know how to edit it to trace across sheets?

 

[Dan_W]

Ahh apologies - you did say across worksheets. What's the caption/name of the pop-up window you want?

 

[CSSouthern]

The exact thing that the code you wrote does. Bring up the Go To box, but it is only popping up with contents for cells that reference cells in the same worksheet. How should i modify the code to be able to pull up references in another worksheet?

 

[Dan_W]

It's working for me. Try selecting the relevant cell with the precedents/dependents and try it again.

 

[Dan_W]

Hi - did you manage to get this to work?

 

[mikerickson]

This doesn't invoke the GoTo dialog box, but it does take you to the off sheet president ranges.
Select the cell with the formula and run this

Sub test()
    Dim myCell As Range, onePrecedent As Range
    Dim i As Long
    Set myCell = Selection.Cells(1, 1)
    
    myCell.ShowPrecedents
    i = 1
    
    Do
        On Error GoTo Out
        Set onePrecedent = myCell.NavigateArrow(True, 1, i)
        On Error GoTo 0
        Select Case MsgBox("Precedent #" & i & vbCr & "at " & onePrecedent.Address(, , , True), vbOKCancel)
            Case Is = vbCancel
                Exit Sub
        End Select
        i = i + 1
    Loop
Out:
    Application.Goto myCell
    myCell.Parent.ClearArrows
End Sub

 

[CSSouthern]

Thanks all for the help! I decided to download Arixcel. The license was only $24 for a full year of access and the "explore precedents" was exactly what I needed. Let me know if there is something bad about the plug-in that I don't know - i.e. viruses.