How to protect worksheet with writeable cells that retain formatting and comments

Question

Is there a way to protect a worksheet such that certain cells are writeable (e.g. user can put numbers into these cells), but the formatting and comment box cannot be changed? I am able to create a protected worksheet with only certain cells unlocked. However, if I copy/paste from a cell in another worksheet (that has different formatting and it's own comment box) into those cells, the formatting and comments are overwritten.

The solution would ideally work in Excel 2010, but newer-version-specific solutions would also be interesting to me.

Answer 1

In this case I find that I need to give a negative answer : The feature you are asking for is impossible without a VBA macro. If you're interested in going that route, below is a simple snippet of code that will try to perform a normal excel paste, and in case of an error tries to print from the clipboard.

Public Sub PasteasValue()
    On Error GoTo errhand
        If ActiveWorkbook Is ThisWorkbook Then
        Selection.PasteSpecial Paste:=xlPasteValues
            End If
        If Not ActiveWorkbook Is ThisWorkbook Then
        Selection.Paste
            End If
    Exit Sub
    
errhand:
    If ActiveWorkbook Is ThisWorkbook Then
    ActiveCell.PasteSpecial xlPasteAll
        End If
    If Not ActiveWorkbook Is ThisWorkbook Then
    ActiveCell.PasteSpecial xlPasteAll
        End If
End Sub

I base my answer on more than a decade that this feature was demanded by Excel users, but a solution never came.

As evidence I list below some of the many suggestions found on the Excel User Voice site of Microsoft, voices that were always ignored :

• Change Excel's paste default behavior
• Stop Copy & Paste overwriting Data Validation
• Need ability to allow only paste values in protected Excel without using macros or vba
• Add an option to change the default for "Paste" so that it can be "Formulas" or Values"
• paste as text
• Option to designate default paste to Destination Formatting
• Make "Paste and Merge Conditional Formatting" the Default Paste Behavior

There is some recent evidence (as of Dec 2020) that the Excel dev team may finally be listening, but after more than a decade it's anybody's guess.

You will find in the comments all sort of workarounds that worked for some users, but they all require some discipline from the users, or VBA macros to modify the behavior of Excel for the worksheet.

Answer 2

First, format the cell or ranges the way you want them, also with any comments you like.

Second, Right-click cells intended for user input ==>>"Format cells"==>>"Protection" tab==>>Uncheck "Locked"

Third, "Review Tab==>>Protect Sheet" making sure the option to change formats of cells, columns, rows and edit objects & edit scenarios are all unchecked. And you might want to uncheck "Select locked cells" too, if you want the users to be able to TAB between the cells that they can enter data into, so these spots become obvious to them when they TAB.

EDIT: Someone pointed out that formats could still be copied into the few cells that are unlocked.

So you do this: Make the worksheet look like you want. Unlock the particular cells you want the user to put data into, and protect the worksheet. Copy the worksheet. On the 2nd copy, Make the few cells where user input would normally go refer to the corresponding unlocked cells from the 1st worksheet. Now lock every cell on the 2nd worksheet.

The user could format unlocked cell A1 on the 1st worksheet any wonky way, but the matching cell A1 on the locked down 2nd sheet will only contain the value of "=Sheet1!A1" yields from the 1st worksheet, no formats or comments.

The user can abuse the 1st worksheet, copy & pasting from Nyancat styled webpages, but your 2nd worksheet will show those values in whatever format you chose. The user might still enter bad data, or something like an SQL injection, but at least if the users are trusted enough to enter just data text & value numbers your problem should be solved.