How to unlock specific cells when data is entered?

[will0787]

Hi there,

I need to create a spreadsheet where I want to force the user to enter data in the right order.

So, for example, I want cell B5 to be locked UNTIL data is entered in A3, A4 and A5, BUT for the remainder of locked cells - column C say - to stay locked.

I recently found this answer to a similar problem:


"The first thing you need to do is to protect the worksheet. By default all cells are "locked". Select the cell(s) you wish your user to enter data into. Right click and choose Format Cells. In the Format Cells window select the Protection tab. De-select the Locked checkbox and click OK.

I usually fill the user data entry cell with the pale yellow color to draw the users eye to that cell(s). With the desired cell unlocked, go to the Tools menu and select Protection - Protect Sheet... You can accept the default and click OK. For this example I did not select any other attributes nor added a password.

Open the VBA Editor (Alt+F11) and on the right in the Project-VBAProject pane locate your Workbook in the tree. Open the Microsoft Excel Objects and select your Worksheet.

Paste this code in the code pane:

Please Login or Register  to view this content.

Note: change

Please Login or Register  to view this content.

to reflect your user cell.

Still in the VBA Editor in the Microsoft Excel Objects for your Workbook locate ThisWorkbook. Paste this code in the code pane:

Please Login or Register  to view this content.

When the WB is closed it is set back to Protected.

Save the Workbook"


However, this turns off protection for the entire sheet once data has been entered.

Does anyone know how to change this so that only a certain range of cells are unlocked, not eveything?


Thanks very much for any help!

[davesexcel]

You need to unlock the cells before you protect the sheet
Check this out:
http://www.j-walk.com/ss/excel/faqs/protectionFAQ.htm

[will0787]

You need to unlock the cells before you protect the sheet
Check this out:
http://www.j-walk.com/ss/excel/faqs/protectionFAQ.htm

Thanks for your response, but I don't think you read my post clearly.

I am aware that you have to unlock cells before you protect them.

What I am seeking is a way to make a certain set of cells locked UNTIL data is entered in another set of cells - at which point they would become unlocked.

However, the solution I included in my original post has the drawback of turning off protection for the entire sheet, rather than unlocking specific few cells.

Does anyone know how to do this?

[MartinShort]

Will

The procedure is:

1. Unprotect the sheet
2. Unlock/lock the cell
3. Protect the sheet

- exactly same as if you were doing this manually.

To do this in code for the cell A1 try:

Please Login or Register  to view this content.

HTH
Martin