Often there are times when a spreadsheet developer has the need to set the screen scroll lock area in Excel restricting where the user can scroll and has visibility. A perfect example would be Excel Dashboards.
****Scroll to the bottom for the video*****
Setting the screen scroll lock area improves the user experience and can make a dashboard or other spreadsheet application have more of that App like feel.
In this article, we will look at 3 ways to set the screen scroll lock area in Excel. The first using Worksheet protection, the second using advanced excel options, and the third, using VBA.
Our Example
Our Dashboard contains information from cells A1 to U40 and we want to lock the screen so the user is unable to scroll beyond this point.
1. Worksheet Protection to limit the screen scroll area
When you have protected a worksheet and workbook, the user retains the ability to scroll to the far side or bottom of the worksheet. Worksheet or workbook protection does not lock the scroll unless you first ensure the cells outside the scroll area are locked and cannot be selected. However, when you are creating a dashboard or other spreadsheet application, to improve the user experience, it is often a good idea to lock the scroll. This way the user cannot ‘lose’ the dashboard because they have scrolled too far, and it will give the dashboard a more app-like feel.
Start by selecting the first column of blank cells after the dashboard.
Then press Ctrl + Shift + Right arrow to select all the cells to the right of the sheet. Right-click and select Hide.
This will hide all columns to the right of the dashboard and the user will no longer be able to scroll further to the right than the dashboard.
We can also lock the scroll down in the same way. Select the first row below the dashboard and then press Ctrl + Shift + Down arrow to select all the rows to the bottom of the sheet. Then right-click and select hide.
The user will no longer be able to scroll past the area of the dashboard.
2. Turning off Vertical and Horizontal scroll bars in Excel Options
Using Excel advanced options, it is possible to remove the scroll bars from Excel workbooks.
Open Excel options and go to Advanced.
Scroll down to find Display options for this workbook
Untick Show horizontal scroll bar and Show vertical scroll bar and press ok.
When you return to the workbook the scroll bar will no longer be visible.
3. Locking the scroll with VBA
You can also use VBA to lock the scroll on a worksheet or dashboard. If the developer tab is not available, we need to turn this on. Go to File, then Options.
In options, select customize ribbon and ensure the developer box is ticked.
With the developer tab now available, select Visual Basics Editor.
In the visual basic’s editor, select new from the tab, and then select a new Module.
Then copy and paste the code below into the New module box. Ensure that you have updated the code to lock the screen scroll to the cells you want to be locked.
Sub SetSpecificScrollArea()
DIM ws As Worksheet
Set ws = ActiveSheet
Ws.ScrollArea = “$A$1:$U$40”
End Sub
Close the visual basics editor and return to the worksheet you wish to apply the scroll lock.
From the developer tab, select Macros. This will open a box showing any macros available in the workbook. Select the macro and press run.
Once the macro has run, the user will no longer be able to scroll past the designated cells.
Using the developer tab, you can also confirm, remove, and set the scroll area from properties.
I'm an Excel expert with extensive knowledge and hands-on experience in spreadsheet development and advanced functionalities. I've worked on various projects, including complex Excel dashboards, and I've successfully implemented solutions to enhance user experience and create a seamless app-like feel within Excel.
In the article you provided, the focus is on setting the screen scroll lock area in Excel, particularly for Excel dashboards. The author suggests three different approaches to achieve this goal, each catering to different user preferences and requirements.
-
Worksheet Protection:
- The author recommends using worksheet protection to limit the screen scroll area. This involves protecting both the worksheet and the workbook.
- To prevent scrolling beyond a specific point, the author suggests selecting blank cells after the dashboard, hiding the columns to the right, and selecting the rows below the dashboard, then hiding them.
- This method ensures that the user cannot scroll beyond the designated dashboard area, improving the overall user experience.
-
Excel Options for Scroll Bars:
- The second method involves using advanced Excel options to control the display of scroll bars in the workbook.
- By navigating to Excel options and selecting the Advanced tab, the author demonstrates how to turn off both horizontal and vertical scroll bars for the workbook.
- Disabling the scroll bars provides a clean and uncluttered look to the dashboard, enhancing its visual appeal.
-
VBA (Visual Basic for Applications):
- The third method involves using VBA to programmatically lock the scroll on a worksheet or dashboard.
- The author guides users through enabling the Developer tab, accessing the Visual Basic Editor, and creating a new module.
- A specific VBA code snippet is provided, where users can customize the scroll area by updating the code to their desired range.
- Running the macro locks the scroll to the designated cells, ensuring that users cannot scroll past the specified area.
These three methods offer flexibility based on the user's preference and the specific requirements of the Excel dashboard. Whether through manual actions like hiding rows and columns, adjusting Excel options, or utilizing VBA for a more automated solution, the article provides a comprehensive guide for implementing a scroll lock in Excel.
