Excel VBA: Lock cells without protecting the workbook

Question

I have created an add-in that sends and retrieves data from a database in order for this data to be used by our analysts. To prevent changes made to existing data points I want to lock the cells containing this data. Initially, I did this by locking the range of the data and protecting the workbook, since otherwise the locking does not work. However, protecting the workbook also removes/limits a lot of functionality for the end-user, such as creating graphs, the auto fill function, changing the format etc. Since these and other functionalities are needed for the end-user, I cannot protect the workbook. Still, I want to lock the cell containing the data points.

So my question is, is it possible to lock the cells in a dynamic range (I have macros detecting the correct end column and end row of the data points) without protecting the workbook? If so, how? If not, would it be possible to detect changes in the dynamic range and show a messagebox that changes are not allowed in this specific cell/range and revert back to the old value of the cell? If so, how?

I have seen a few posts asking a similar question, but these were either not answered or the answer was not satisfying for my case (e.g. a macro implemented in the VBA project of the workbook instead of the VBA project of the add-in).

Thanks in advance for your answer(s)!

Kind regards, Robbert

Answer 1

Use

ActiveSheet.Cells.Locked=False

Then Lock your range which you don't want to be edited using:

Range("A1:A4").Cells.Locked=True

Then protect the sheet

ActiveSheet.Protect Contents:=True, DrawingObjects:=False

This will only protect the contents that are Locked and still allow you to change values in the rest of the sheet and insert/delete charts. You may have to change extra settings with Protect. More details can be found here

Answer 2

You don't have to protect the Workbook, only the Worksheet.

To protect/unprotect a sheet via VBA, use Worksheet-methods protect resp. unprotect (you can apply a password).

To prevent a range to be modified, you have to set it's locked-property to True and protect the sheet (all cells have the propery locked set by default).

Be aware that, if a Range is locked and the worksheet is protected, you cannot modifiy the Range via VBA. If you want to do so, use the unprotect method at the top of the code (but don't forget to protect the sheet again when you're done). As an alternative, you can call protect with parameter UserInterfaceOnly:=True, but unfortunately Excel doesn't save this property. So if you save an Excel file where a worksheet is protected with UserInterfaceOnly:=True and reopen it, the sheet is protected also for VBA.