Find Circular References

[jgarland]

I found the following code online and slightly altered it for my use. I don't remember the source or I would give credit. The code loops through all sheets in the Workbook and finds circular references. This works perfectly but I need it to show a message if no circular references were found.

Sub FindCircularReferences(control As IRibbonControl)
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim msg         As String

    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.CircularReference
        If Not rng Is Nothing Then
            rng.Parent.Select
            rng.Select

            msg = "Circular reference found on sheet " & ws.Name & _
                  " in cell " & rng.Address(0, 0) & _
                  " with formula: " & vbLf & rng.Formula

            MsgBox msg, vbCritical, "Circular Reference Found"
            Exit For
        End If
    Next ws
MsgBox "Nothing Found", vbInformation, "Circular Reference" ' my added code
End Sub

I added a message box at the end of this code but it fires every time a reference is found. If I have multiple circular references then I need it to fire only after the last reference is found. Any and all help is greatly appreciated.

 

[Jimmypop]

Hi there

I think you wold just need to move your message and add a Else Statement... test the below and let us know?

Sub FindCircularReferences(control As IRibbonControl)
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim msg         As String

    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.CircularReference
        If Not rng Is Nothing Then
            rng.Parent.Select
            rng.Select

            msg = "Circular reference found on sheet " & ws.Name & _
                  " in cell " & rng.Address(0, 0) & _
                  " with formula: " & vbLf & rng.Formula

            MsgBox msg, vbCritical, "Circular Reference Found"
            Exit For
            Else
            
         MsgBox "Nothing Found", vbInformation, "Circular Reference" ' my added code
        End If
    Next ws

End Sub

 

[Artik]

Or

(...)
Next ws

If Len(msg) > 0 Then
    MsgBox "Nothing Found", vbInformation, "Circular Reference"
End If

End Sub

Artik

 

[jgarland]

Thanks for the quick response.

Both Jimmypop & Artik revisions do not trigger the message if no circular references are found. Both revisions also will trigger the message after every circular reference is found.

What I am looking for is:
A. If no circular references are found, then show the "Nothing Found" message.
B. If a circular reference is found then I need the code to exit without triggering the message. This way I can eliminate the reference after each occurrence.

I just need the "Nothing Found" message to trigger one time, when no references are detected.

 

[Artik]

Oops, meant to be

If Len(msg) = 0 Then

Artik

 

[jgarland]

Oops, meant to be

If Len(msg) = 0 Then

Artik

That did it - works perfectly. Thank you so much.