Turn off error checking on a sheet

Question

Hi

I have a report within a sheet. It works / looks fine on my PC.

However when I send the spreadsheet to others and they open it the sheet displays lots of error checking symbols in each cell

Is there a way that I can setup a sheet to turn off error checking when another user opens it - i guess i want it to be specific to my spreadsheet rather that the users setting for all their sheets?

Thanks

Answer 1

The code will turn off error checking when your workbook is opened or activated, and restore the original settings, whatever they were, when your workbook is closed or deactivated (i.e. the user switches to another workbook).

Answer 2

The error-checking options are user-level and application-wide settings (in the Formulas tab of File > Options). Even if you turn them off, that doesn't affect what other users see.

You could run code to do what you want, but that would require users to allow macros, which might be problematic.

In the ThisWorkbook module:

Private blnBackgroundChecking As Boolean

Private blnEmptyCellReferences As Boolean

Private blnEvaluateToError As Boolean

Private blnInconsistentFormula As Boolean

Private blnInconsistentTableFormula As Boolean

Private blnListDataValidation As Boolean

Private blnNumberAsText As Boolean

Private blnOmittedCells As Boolean

Private blnTextDate As Boolean

Private blnUnlockedFormulaCells As Boolean

Private Sub Workbook_Activate()

    CheckingOff

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    RestoreChecking

End Sub

Private Sub Workbook_Deactivate()

    RestoreChecking

End Sub

Private Sub Workbook_Open()

    CheckingOff

End Sub

Private Sub CheckingOff()

    With Application.ErrorCheckingOptions

        blnBackgroundChecking = .BackgroundChecking

        blnEmptyCellReferences = .EmptyCellReferences

        blnEvaluateToError = .EvaluateToError

        blnInconsistentFormula = .InconsistentFormula

        blnInconsistentTableFormula = .InconsistentTableFormula

        blnListDataValidation = .ListDataValidation

        blnNumberAsText = .NumberAsText

        blnOmittedCells = .OmittedCells

        blnTextDate = .TextDate

        blnUnlockedFormulaCells = .UnlockedFormulaCells

        .BackgroundChecking = False

        .EmptyCellReferences = False

        .EvaluateToError = False

        .InconsistentFormula = False

        .InconsistentTableFormula = False

        .IndicatorColorIndex = False

        .ListDataValidation = False

        .NumberAsText = False

        .OmittedCells = False

        .TextDate = False

        .UnlockedFormulaCells = False

    End With

End Sub

Private Sub RestoreChecking()

    With Application.ErrorCheckingOptions

        .BackgroundChecking = blnBackgroundChecking

        .EmptyCellReferences = blnEmptyCellReferences

        .EvaluateToError = blnEvaluateToError

        .InconsistentFormula = blnInconsistentFormula

        .InconsistentTableFormula = blnInconsistentTableFormula

        .ListDataValidation = blnListDataValidation

        .NumberAsText = blnNumberAsText

        .OmittedCells = blnOmittedCells

        .TextDate = blnTextDate

        .UnlockedFormulaCells = blnUnlockedFormulaCells

    End With

End Sub

Edited by HansV MVP to remove incorrect lines.

Answer 3

Thanks very much

Answer 4

Thanks

Will this work if the user had already disabled error checking eg would this only impact this workbook or would it potentially affect their settings ?