protection vs validation

[merlin_the_magician]

I make quite a few spreadsheets, intended to be filled out by others. To make them user-friendly (or idiot- proof, as you like…), I like to use data validation for input information, error messages and data limitations.
Upon selecting a cell, users are presented with information the content and/of allowed data entry. When they enter invalid values, they are kindly remembered why and/of what is wrong. Just brilliant. However…

Things start going wrong whenever a formula-cell is validated. Usually, these are hands off. Validation to inform users should do the trick, but.... there are always a few users that, either by mistake, ignorance or incompetance, manage to make a mess of your spreadsheet, regardless of your careful precautions.
When a user decides to delete or overwrite, the formula is gone. Unfortunately, cell validation does allow formulas to be deleted and/or overwritten.

Of course, this can be solved by simply protecting a worksheet or workbook. This, on its turn however, limits functionality of validation.
An attempt to delete a cells value, will result in a brutal popup waring about limitation due to worksheet protection, ignoring validation information.

Is there any way validation and sheet protection can work together better?

 

[BlueAure]

Hi merlin,

Have you tried using:

Sheets("Sheet1").Protect Password:="Password", UserInterFaceOnly:=True

? It protects a sheet from user edits only. I have not used this with data validation, but it allows macros to work even when a sheet is protected. It might help with your issue.

 

[Steve_]

I make quite a few spreadsheets, intended to be filled out by others. To make them user-friendly (or idiot- proof, as you like…), I like to use data validation for input information, error messages and data limitations.
Upon selecting a cell, users are presented with information the content and/of allowed data entry. When they enter invalid values, they are kindly remembered why and/of what is wrong. Just brilliant. However…

Things start going wrong whenever a formula-cell is validated. Usually, these are hands off. Validation to inform users should do the trick, but.... there are always a few users that, either by mistake, ignorance or incompetance, manage to make a mess of your spreadsheet, regardless of your careful precautions.
When a user decides to delete or overwrite, the formula is gone. Unfortunately, cell validation does allow formulas to be deleted and/or overwritten.

Of course, this can be solved by simply protecting a worksheet or workbook. This, on its turn however, limits functionality of validation.
An attempt to delete a cells value, will result in a brutal popup waring about limitation due to worksheet protection, ignoring validation information.

Is there any way validation and sheet protection can work together better?

I use this to 'write protect' my formulas.

Public CellFormula As Variant
Public isUpdating As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CellFormula = Empty
    ReDim CellFormula(Target.Count - 1, 1)
    i = 0
    For Each tCell In Target
        If Left(tCell.Formula, 1) = "=" Then
            CellFormula(i, 0) = tCell.Address
            CellFormula(i, 1) = tCell.FormulaR1C1
        End If
        i = i + 1
    Next tCell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not isUpdating Then
        i = 0
        For Each tCell In Target
            If CellFormula(i, 1) <> "" Then
                isUpdating = True
                tCell.FormulaR1C1 = CellFormula(i, 1)
            End If
            i = i + 1
        Next tCell
    End If
    isUpdating = False
End Sub

 

[BlueAure]

If you are using macros, then my earlier protect function may help. It allows macros to run, but not users to edit ranges.