protection vs validation

merlin_the_magician

Active Member
Joined
Jul 31, 2002
Messages
480
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?
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
If you are using macros, then my earlier protect function may help. It allows macros to run, but not users to edit ranges.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top