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.
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.
Over to you
Describe a situation where you would find locking the screen scroll in Excel useful to you. Drop and comment below and you could earn some rewards.
I would suggest that you sign up directly for your own hive wallet and use this to sign into steempress comments below. This way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
If you sign up using the comments section below you will get a Steempress account. Steempress will hold any rewards you earn until you have a hive wallet.
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles
Do you want to start collecting rewards quickly for learning Excel? Then you should try:
10+ Excel Learn and Earn Activities YOU can do Today
Cross posted from my blog with SteemPress : https://theexcelclub.com/3-ways-to-set-screen-scroll-lock-area-in-excel/
Usually when you create a "form" for users to input data. I use access for this, but you can link to a SQL database via Excel also... If I'm remembering correctly? (It's been a while)
You can create a data entry form in excel that writes to an excel table too. I dont use assess and hardly ever sql
Congratulations @theexcelclub! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :
You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOP