Count number of cells with data validation errors

[Gareth]

Hello all,
I have been using the code below to count the number of data validation
errors on a sheet (datasheet). It has worked sucessfully over a number of
years but all of a sudden when it is run the following message is displayed
and the file closes:
EXCEL.exe has generated errors and will be closed by Windows.
You will need to restart the program.
An error log is being created.

macro:
Sub DVerrors()
Application.ScreenUpdating = False
Sheets("datasheet").Activate
Dim rngDV As Range
Dim cell As Range
Dim countDV As Long
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
countDV = 0
For Each cell In rngDV
If Not cell.Validation.Value Then
countDV = countDV + 1
End If
Next
If countDV = 0 Then
MsgBox "There are no data validation errors on the sheet.",
vbInformation, "Invalid data entries"
ActiveSheet.ClearCircles
ElseIf countDV = 1 Then
MsgBox "There is " & countDV & " data validation error on the sheet.",
vbInformation, "Invalid data entry"
ActiveSheet.CircleInvalid
ElseIf countDV > 1 Then
MsgBox "There are " & countDV & " data validation errors on the sheet.",
vbInformation, "Invalid data entries"
ActiveSheet.CircleInvalid
End If
Exit Sub
errhandler:
MsgBox "There are no data validation errors on the sheet.", vbInformation,
"Invalid data entries"
ActiveSheet.ClearCircles
Application.ScreenUpdating = True
End Sub

Thanks in advance.

Gareth

 

[Jim Cone]

Gareth,
Your code worked for me on xl2002 (with a small number of validated cells).
Some comments...

SpecialCells has a limit of 8192 areas that it can return.
If you have thousands of validated cells on your worksheet you may be over the limit.

It is usually best to loop thru each area in the range returned by SpecialCells...
For Each rArea in ws.Cells.SpecialCells(xlCellTypeAllValidation).Areas
For Each rCell in rArea.Cells

If the problem is limited to just one workbook, it could be corrupt.
You would need to build a new workbook.

xl2007 has its own rule book designed to encourage users to upgrade from older versions.
--
Jim Cone
Portland, Oregon USA



"Gareth"
wrote in message
Hello all,
I have been using the code below to count the number of data validation
errors on a sheet (datasheet). It has worked sucessfully over a number of
years but all of a sudden when it is run the following message is displayed
and the file closes:
EXCEL.exe has generated errors and will be closed by Windows.
You will need to restart the program.
An error log is being created.

macro:
Sub DVerrors()
Application.ScreenUpdating = False
Sheets("datasheet").Activate
Dim rngDV As Range
Dim cell As Range
Dim countDV As Long
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
countDV = 0
For Each cell In rngDV
If Not cell.Validation.Value Then
countDV = countDV + 1
End If
Next
If countDV = 0 Then
MsgBox "There are no data validation errors on the sheet.",
vbInformation, "Invalid data entries"
ActiveSheet.ClearCircles
ElseIf countDV = 1 Then
MsgBox "There is " & countDV & " data validation error on the sheet.",
vbInformation, "Invalid data entry"
ActiveSheet.CircleInvalid
ElseIf countDV > 1 Then
MsgBox "There are " & countDV & " data validation errors on the sheet.",
vbInformation, "Invalid data entries"
ActiveSheet.CircleInvalid
End If
Exit Sub
errhandler:
MsgBox "There are no data validation errors on the sheet.", vbInformation,
"Invalid data entries"
ActiveSheet.ClearCircles
Application.ScreenUpdating = True
End Sub

Thanks in advance.
Gareth

 

[Gareth]

jim
Thanks for that. My sheet can have up to 20,000 cells with validation. Are
you able to amend my macro so that it will look at all dv cells instead of
only the first 8,000 or so just in case it is this that is causing the
program error?
Thanks.
Gareth

 

[Jim Cone]

You could split the UsedRange on the worksheet into 3 or 4 parts
and use the SpecialCells method on each separate part.
That should eliminate the 8192 area limit.
To test whether that can/will cure the problem,
you can just change one line of your code...
Change:
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
To:
Set rngDV = Selection.SpecialCells(xlCellTypeAllValidation)

Then select say a 1/4 of the cells and run the code.
--
Jim Cone
Portland, Oregon USA



"Gareth"
wrote in message
jim
Thanks for that. My sheet can have up to 20,000 cells with validation. Are
you able to amend my macro so that it will look at all dv cells instead of
only the first 8,000 or so just in case it is this that is causing the
program error?
Thanks.
Gareth