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:
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
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:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
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:
Private Sub Workbook_BeforeClose(Cancel
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!