How to automate the process of locking cells when they contain data

Anonymous
2016-02-24T05:54:42+00:00

In Excel 2003, to protect data entered in cells, and allow only editing empty cells, I do the following:

In an empty worksheet, I select all cells, and Format | cells | protection | uncheck locked property.

After I enter data in some cells, I select these cells and check the locked property.

To protect the entered data from being edited, I protect the sheet by

Tools | Protection | Protect Sheet | Allow users to Select locked cells and Select unlocked cells

Now only the empty cells could be edited.

My question is that:

Is it possible to automate the process of letting the cells to be unlocked when they are empty, and to be locked when they contain data? So that I do not have to select the cells containing data and lock them manually each time I enter data in them.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-02-25T06:37:32+00:00

    The macro from my previous reply should NOT be copied into the worksheet module. It won't be useful there. Instead, copy it into a standard module (the type you create by selecting Insert > Module in the Visual Basic Editor).

    Then run the macro once by clicking anywhere inside it and pressing the function key F5.

    After that, the cells that have a value or formula should be locked. You won't have to run this macro again.

    The worksheet module (the one you activate by right-clicking on the sheet tab and selecting View Code) should contain the code from my first reply.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-02-25T03:16:26+00:00

    Hello HansV. Thank you for your time.

    I did the following: 

    I opened my workbook, and clicked on Enable macros.

    I selected all the cells in a sheet (some of them contained data, some contained formulae, some were empty), and unlocked them, then protected the sheet. with the password secret.

    Right-clicked the sheet tab and selected View Code from the context menu, and pasted your new code, then saved the workbook and closed it. I opened the workbook, and clicked on Enable macros. Still all the cells (those containing data or formulae and those which are empty) could still be edited.

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2016-02-24T14:10:49+00:00

    Hello  HansV. Thank you for your reply. I tried your technique and code in Excel 2003, but unfortunately it only dimmed the option of Format | cells and conditional formatting; but it did not lock the cells containing data, they still could be edited.

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more