MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Locking Cells to Protect Critical Data


October 12, 2001 - by Bill Jelen

Frank from Holland posed today's question:

Hello, I want to make a sort of database in Excel. Therefore, I need to find a Macro or something like that, with which I can create a command that makes the cursor jump from (for example) A16 to B2 after using the enter-button on A16. Would you know the solution for this problem? Thanks!

One easy way to achieve this is to make A16 and B2 the only unlocked cells on the worksheet. By default, all cells are locked. You can use Format - Cells - Protection - uncheck the Locked checkbox to unlock the cells.

In Excel 2000 and earlier you can simply protect the worksheet with Tools - Protection - Protect Sheet. In Excel 2002 you will have to use Tools - Protection - Protect Sheet and then uncheck the option for "Select locked cells".

After the sheet is successfully protected, hitting enter in one unprotected cell will take you to the next unprotected cell.

Note

If protecting the worksheet is not practical, you would have to resort to a Worksheet_Change macro that sensed which cell just changed.

Here is a sample macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$2"
            Range("A16").Select
        Case "$A$16"
            Range("B2").Select
    End Select
End Sub

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.