a macro implemented in the VBA project of the workbook instead of the VBA project of the add-in). 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. 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?
Still, I want to lock the cell containing the data points. Since these and other functionalities are needed for the end-user, I cannot protect the workbook. 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. Initially, I did this by locking the range of the data and protecting the workbook, since otherwise the locking does not work.
#EXCEL 2010 PROTECT CELLS WITHOUT PROTECTING SHEET CODE#
The code will check the first cell from above the selected cell, it will reprotect the table if that cell is protected: (Off, 0).Locked False. To prevent changes made to existing data points I want to lock the cells containing this data. To protect those columns, I simply set manually the Locked property of the cell to Locked (from the right click menu, Format cells, Protection tab, check the Locked checkbox). Go to File > Info > Protect > Unprotect Sheet, or from the Review tab > Changes > Unprotect Sheet.
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 unprotect a sheet, follow these steps: Go to the worksheet you want to unprotect.