A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
We all know that for some problems, Protect Sheet tool under the REVIEW tab doesn’t cut it. Well, Jordan has found another way to restrict end-user access and it has its own advantages.
The way this technique works is by restricting users to a specific range within a sheet by limiting ‘Scroll Area’. For example, follow these steps:
For illustration, we will be locking in “A1:K20” (see the image below). Now users cannot scroll down, neither can they click on any cell outside this range. Note that any contiguous range can be selected instead of “A1:K20”.
Benefits include getting rid of the need to unlock cells and enable sheet protection. Also, some features that are disabled by sheet protection would available here. The only problem is that the lock on scroll area disappears when you reopen the file. But we have a solution to this problem as well.
Click on DEVELOPER > Visual Basic and then paste the following code in “ThisWorkbook”:
Note that “A1:K20” is just an example array. You can type in any other array as long as it is contiguous.
For people unfamiliar with event-driven programming in VBA: when you open a workbook, Excel calls it by event name “Workbook_Open”. And whatever code is written under this event name gets executed without the user asking for it. Hence, upon opening your Excel file, Sheet1 will always get a scroll lock on “A1:K20”. Other such events include “Worksheet_Activate”, “Worksheet_Deactivate” and “Workbook_Deactivate”.
This technique of restricting user access is useful in the following cases:
The last case can also be dealt with through the use of ‘One Click UserForm’, a popular product to enable safe data entry.
The only problem now is that if your array is discontinuous, you cannot use this method. Of course, you can always resort to the classical sheet protection method in this case.
Start tapping away at your keyboard and try this technique out ASAP! I am sure you can put it to some amazing use in your own work.
Do not forget to share this cool trick with your colleagues. And write your thoughts to us in the comments section below.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide