Hi jan Karel,
Brilliant as always!

Many thanks!

Kind Regards,
Robert
--
Rodels! Creating wonderful weapons of Maths Destruction since 1998!


"Jan Karel Pieterse" wrote:

Hi Rodels,

I want to create an alert that flashes a msgbox when a sheet is activated
and contains data that is flagged as invalid by using the validation circles.

In a large sheet, it is tricky to find all the cells that may contain
invalid data which is why I want to be able to create a msgbox that lists the
addresses or something like that.

By looping through the shapes in the sheet, I can determine that these
errors exist but I can't find out where they are as the data validation oval
does not appear to have a .topleftcell location.

I'd use a different approach altogether:

Sub FindViolations()
Dim oRng As Range
Dim oCell As Range
Dim oInvalid As Range
On Error Resume Next
Set oRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation)
On Error GoTo 0
If oRng Is Nothing Then Exit Sub
For Each oCell In oRng
If oCell.Validation.Value = False Then
If oInvalid Is Nothing Then
Set oInvalid = oCell
Else
Set oInvalid = Union(oInvalid, oCell)
End If
End If
Next
If Not oInvalid Is Nothing Then
oInvalid.Select
MsgBox "Validations violated in cells: " & oInvalid.Address
Else
MsgBox "No validations violated"
End If
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

.