Auto lock cells after data entered and SAVED

[Ljung]

Hi, I have a workbook, with different sheets.
Some of the sheets (containing input cells as well as formula cells) I want to :

- lock cells which are not blank (with data or formulae) after file is saved, users will be prompt for password if changes need to be made on those cells
- the whole sheet still can be selected and copied (then paste at another worksheet, this will be handled by macro)
- user still able to add copy and paste rows containing both blank and cells with data/formula

can this be handled by excel buttons or need to go to macro level?


thank you very much in advance.

[dflak]

This would have to be handled by VBA using a BeforeSave event on the workbook.

The code is going to have to loop through the cells to determine if the cells have any content. This can be a huge undertaking for an entire sheet. So it would help if there were a way to limit the range that needs to be checked. Excel Tables might help since they "know" how many rows and columns they contain.

When you protect a sheet, you can enable things like sorting and adding rows or columns. I would record a macro that does this and clean it up and put it in the code. To unprotect the sheet, you can require a password.

To lock and unlock cells you will have to unprotect the sheet, change the cells and protect the sheet again. This can be done in VBA, but the password will be clearly visible in the code. So you should also password protect the code itself.

[Ljung]

Hi dflak,

sorry maybe I need to rephrase my title.
Without password, user would be able to key in empty cell, copy and paste rows (with some of the columns locked).
Without password, whole page can be copied and paste at another dummy sheet by macro working background for report generating (a separate part I am working on).

And after this workbook is saved, all cells with input and formula will be locked (user prompt for password to edit).

thanks for the lead, will try and explore on it.


While replying to this post, another idea just popped up in my head....