VBA Archives

Sometimes you may want to limit the access of a user to a particular range of cells in a worksheet, for example you may have a worksheet set up that only a certain range can be modified or activated -perhaps data entry cells or formula cells. It's really easy to do this and can control user access to the cells you choose. There are a couple of ways to to this. Setting the scroll area property 1. First of all you need to make sure the Developer Tab is visible. If it is not Excel Options Popular Show Developer Tab 2. Select Developer 3. Controls 4. Properties 5. In the properties window enter the scroll area you want, this has to be entered manually (typed) in this example its B2:D10 After you have done this, you can then only scroll those few cells and cannot activate anything outside that area. The VBA Method Now then, unfortunately this isn't a persistent feature and is reset when you save your file, close it then reopen. Annoying. Well, with a little bit of VBA code we can achieve a more permanent scroll restriction. 1. Hit ALT + F11 2. Click on ThisWorkBook in the Project VBA Window 3. You will now see the This Workbook code module- double click to open it 4. Enter or copy the following code into This Workbook code module Private Sub Workbook_Open() Worksheets("Sheet1") . ScrollArea = "B2:D10" End Sub 5. Press ALT+F11 to return to Excel 6. Save the workbook, re open it. If your workbook has an .xlsm extension it needs to be saved as as macro enabled workbook (.xlsm) extension. Other Excel Tips You Might Like 1. Restrict the number of characters a user can enter into a cell 2. Advanced filtering in Excel 3. Transpose data from rows to columns in a flash 4. Introducing conditional formatting

How To Excel At Excel

Category: VBA