Way to Turn Off Error Checking on a Range?

[James Cox]

Is there a way to turn off error checking on a range? It can be done by
clicking on the little error symbol and selecting the option to ignore the
error ("Yes, I know that empty cells were in the sum formula but it's
OK!!!!"), but when I try to record this action as a keystroke macro, I get
nothing

Any wisdom out there on this?

James

 

[Vasant Nanavati]

Range("A1").Errors(xlEmptyCellReferences).Ignore = True

Unfortunately, it doesn't seem to work on multi-cell ranges, at least for
me, so you may have to loop through every cell in the range.

 

[James Cox]

Thanks, Vasant -

I got trapped in a meeting where my attendance but not participation was
required (yes, I DO work with Dilbert) and scratched out the following code
that works pretty well - just highlight the range of cells and run the
applicable macro (as usual, be wary of the the effects of line-wrap in this
message!) :


Public Sub cancelErrorCheck()


'Possible values of xlErrorChecks
'xlEvaluateToError = 1
'xlTextDate = 2
'xlNumberAsText = 3
'xlInconsistantConstant = 4
'xlOmittedCells = 5
'xlUnlockedFormulaCells = 6
'xlEmptyCellReferences = 7

Dim origCell As Object
Dim iI As Integer

'Take 'em all out
For Each origCell In Selection
For iI = 1 To 7
origCell.Errors(iI).Ignore = True
Next iI
Next origCell


End Sub


Public Sub restoreErrorCheck()

Dim origCell As Object
Dim iI As Integer

'Put 'em all back in
For Each origCell In Selection
For iI = 1 To 7
origCell.Errors(iI).Ignore = False
Next iI
Next origCell


End Sub


Public Sub checkErrorCheck()

Dim origCell As Object
Dim iI As Integer
Dim strErrorChecks As String
Dim bErrCk1 As Boolean
Dim bErrCk2 As Boolean
Dim bErrCk3 As Boolean
Dim bErrCk4 As Boolean
Dim bErrCk5 As Boolean
Dim bErrCk6 As Boolean
Dim bErrCk7 As Boolean

strErrorChecks = ""
bErrCk1 = False
bErrCk2 = False
bErrCk3 = False
bErrCk4 = False
bErrCk5 = False
bErrCk6 = False
bErrCk7 = False

For Each origCell In Selection
For iI = 1 To 7
If origCell.Errors(iI).Ignore = True Then
Select Case iI
Case 1
If bErrCk1 = False Then
bErrCk1 = True
strErrorChecks = strErrorChecks & "
xlEvaluateToError" & vbLf
End If
Case 2
If bErrCk2 = False Then
bErrCk2 = True
strErrorChecks = strErrorChecks & " xlTextDate"
& vbLf
End If
Case 3
If bErrCk3 = False Then
bErrCk3 = True
strErrorChecks = strErrorChecks & "
xlNumberAsText" & vbLf
End If
Case 4
If bErrCk4 = False Then
bErrCk4 = True
strErrorChecks = strErrorChecks & "
xlInconsistantConstant" & vbLf
End If
Case 5
If bErrCk5 = False Then
bErrCk5 = True
strErrorChecks = strErrorChecks & "
xlOmittedCells" & vbLf
End If
Case 6
If bErrCk6 = False Then
bErrCk6 = True
strErrorChecks = strErrorChecks & "
xlUnlockedFormulaCells" & vbLf
End If
Case 7
If bErrCk7 = False Then
bErrCk7 = True
strErrorChecks = strErrorChecks & "
xlEmptyCellReferences" & vbLf
End If
Case Else
strErrorChecks = strErrorChecks & " Some strange
code = " & CStr(iI) & " was detected."
End Select
End If
Next iI
Next origCell

If strErrorChecks = "" Then
MsgBox "No cells in the selected range have any of the Error Checks
turned off."
Else
MsgBox "One or more of the cells in the selected range" & vbLf &
"have the following Error Checks turned off:" & vbLf & vbLf & strErrorChecks

End If
End Sub

Thanks for the starter concept!

James