Circular Reference-does anyone know how to stop a user from creating a circular?

[Sharp]

Hi,

does anyone know how to stop a user from creating a circular reference using VBA code. For example if the user tries to create one a msg pops up saying that the user is not allowed to create circular references

thanks

[rylo]

Hi

This is pretty brutal but perhaps.

Sheet event code

Private Sub Worksheet_Calculate()
  On Error Resume Next
  fred = ActiveSheet.CircularReference.Select
  On Error GoTo 0
  If Not IsEmpty(fred) Then
    MsgBox "No No"
    ActiveCell.ClearContents
  End If
End Sub

If you create a circular reference on the sheet, then it will still show the normal error message, but also give you the additional message, and then clear the formula from one of the cells in the circular reference.

HTH

rylo