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.

Are you the only one using the sheet? If so, you could copy/paste values instead of using the default paste. You could even record a macro when you paste as values and save it as some combination of Ctrl+[blank]+v. — jrichall, Mar 27 '18 at 17:29
Unfortunately this workbook will be distributed broadly. I'm not the only user. So I want to control the experience as much as possible. — Suraj, Mar 27 '18 at 17:43
Without seeing examples of the current state, or what you hope to achieve, I can only guess. But, have you considered creating an Excel form instead of trying to protect certain cells on a sheet? I don't know how much data needs to be entered, or what kind of reporting you need to do. However, I was tasked with gathering information from across 7 different states and an excel form with a button to "send completed form" to my inbox did the trick pretty well. — jrichall, Mar 29 '18 at 22:32
@jrichall - unfortunately that's not an option. I think I described what I hoped to achieve in enough detail, but let me know if I can clarify anything. — Suraj, Mar 31 '18 at 00:57
For older Excel versions try selecting all and entering Tools > Protection > Protect Sheet, and unchecking the "Format Cells" permission. For newer versions this dialog is in Review > Protect Sheet. — harrymc, Mar 31 '18 at 08:47
@harrymc - that's what I've been doing. The "Select locked cells" and "Selected unlocked cells" is checked. Unfortunately, that doesn't prevent formatting and comments from being pasted into the unlocked cells. — Suraj, Apr 01 '18 at 21:32
You need to paste differently. Some ideas to do without a VBA macro : (1) On the Home tab, Clipboard group, click Paste, then click Paste Values. (2) Press Ctrl+Alt+V and in the Paste Special dialog check Values. (3) In other Excel versions use Paste Special with Text. — harrymc, Apr 02 '18 at 07:08
@AceFrahm - VBA is standing in my way of shipping VBA. =) https://boxbase.org/entries/2014/jul/28/vba/ — Suraj, Apr 04 '18 at 12:35

score 3 · Accepted Answer · edited Dec 20 '20 at 10:17

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 :

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.

Thanks for establishing that this is not possible. Solid evidence! — Suraj, Apr 05 '18 at 11:49
I discovered this today... in 2022! and actually yelled at excel. Sheet protection is basically useless for all but the most basic use cases, fixed sections etc. — Michael Dausmann, Aug 09 '22 at 05:04
To supplement this answer, use the code in the answer and follow instructions on this comment to get it to work: https://superuser.com/a/1225457/163809 — ipruthi, Aug 29 '23 at 16:29

Ace Frahm · Answer 2 · 2018-04-04T00:56:45.693

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.

unfortunately this doesn't disallow format or comment pasting. — Suraj, Apr 02 '18 at 19:19
Thanks for thinking out-of-the-box to attempt to solve for this. I'm not excited about the duplicate worksheet; creates a weird user experience. — Suraj, Apr 05 '18 at 11:42

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

2 Answers2