Enter data in protected cell

Copper Contributor

I have a Workbook with a few worksheets with some macro's. The macro's / VBA are protected with a password.
The worksheets are also protected with a password. Some cells in the worksheets can be used to enter data and some cells are locked, mostly because they contain hidden formulas.

Somehow a user has managed to enter data in a protected cell. The formula is gone and overwritten by the entered data.

In my opinion that is only possible bij hacking the worksheet and remove the password.
Or is there another way to do that?

It is Excel 2016 and Windows 10.


 

8 Replies
I've seen odd cases when pasting information into a protected sheet. However, are you sure they didn't just unprotect the sheet?

@Jan Karel Pieterse 

 

I have tried to copy text from another sheet to paste it in the protected cell on the protected sheet. But that is not possible. The message appears that it is a protected sheet. So, no pasting possible. At least, not in Windows 10 / Excel 2016. 
So I thought maybe if you open the workbook with OpenOffice. Maybe then it is possible to paste something?

I have tried to copy and paste it in Excel for Mac, but that is also not possible.

To unprotect the sheet you need the password. 
The only thing to edit the protected cell is by unprotecting with the password (and as I mentioned earlier I am sure the don't have it) or by removing the password with a hacking-program (or something like that).

So, what am I missing here?

OpenOffice might be an app that ignores your protection settings, as Google sheets perhaps?
Yes, indeed. OpenOffice shows all the VBA code but you cannot edit the locked fields. And with Google sheets you can edit the locked fields.
Wow, what is that for protection from Microsoft??

Is it possible to prevent other applications than Microsoft Excel to open a .xlsm file. Perhaps something in the code from Workbook_open? Or something like that?

@gandalf1960 It says more about the compatibility of Google sheets when reading Excel files to be honest. Apparently it does not respect the protection setting.

FYI: Sheet protection is quite poor in Excel. A good reason to separate your input and calculations!

@Jan Karel Pieterse
You write that the poor sheet protection in Excel is a good reason to separate the input and calculations.
What do you mean by that? And how do you do that then?
Not using worksheetfunctions in the sheet but more in the vba-code or?

If you know a better way I am very interested in the how.
It means making a clear and "physical" separation between areas meant for data entry and areas containing calculations. it may mean keeping them on separate worksheets. It is also a good idea to plan ahead: some users are used to using cut and paste, which may wreck existing formulas, even if they are in cells on another worksheet. For such situations it may even be beneficial to have the data entry in a dedicated workbook and the calculations in another workbook.

If there are many users involved (or many not-so-very-Excel-savvy users), I would recommend to rethink the data entry process and look for different technology than Excel, such as survey software (which usually have quite good export features so you can process results in Excel) or perhaps Microsoft Forms.
Thank you for your explanation. Now I know what you meant.
Unfortunately this won't work for me, but thank you for your answers.

I was thinking more about not doing the calculations in the sheet itself, but in the vba code.
And then trigger the vba code to do the calculations when leaving the sheet (or something like that).