Prevent Scrolling on Excel Worksheet

Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling, you can change the ScrollArea property for the worksheet, either manually or with a macro.

Like other Excel protection settings, this is just a deterrent, or a navigation aid, rather than a security feature. Don’t depend on the ScrollArea setting to guard your worksheet!

NOTE: The Scroll Area has to be set each time the workbook opens. To automate this, you can use a macro that runs when the workbook opens. There is sample code below.

Set the Scroll Area Manually

To see how the Scroll Area setting works, you can enter the range address manually.

To manually set the Scroll Area:

  1. On the Ribbon, click the Developer tab, and click Properties
    • scrollarea01
  2. In the Properties window, enter a range address in the ScrollArea property box
    • scrollarea02
  3. Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

To manually clear the Scroll Area:

  1. On the Ribbon, click the Developer tab, and click Properties
  2. In the Properties window, delete the address in the ScrollArea property box
  3. Click on the worksheet, and you will be able to click and scroll freely (assuming that the sheet is not protected).

Set Scroll Areas With a Macro

The Scroll Area settings are cleared when you close the workbook, and you have to set them again, each time the file opens. You can use a macro to set them, as part of the Workbook_Open procedure.

Copy this macro to a regular module in your workbook. It will set the Scroll area for each worksheet in the file, based on the Used Range on that sheet.

Sub SetAllScrollAreas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address
Next ws
End Sub

Add the macro name to the Worksheet_Open procedure in the ThisWorkbook module.

Private Sub Workbook_Open()
    SetAllScrollAreas
End Sub 

scrollarea03

Now, the ScrollArea settings will be added each time the file opens, if you enable macros.

Download the Sample File

To see the code and test worksheets, you can download my sample file. Go to the Worksheet Tips page on my Contextures site, and look for the Download Sample Files section.

Watch the Video

To see the steps for manually or programmatically setting the Scroll Area, please watch this short video.

Or watch on YouTube: Prevent Scrolling on Excel Worksheet

____________________

You may also like...

18 Responses

  1. Roger Govier says:

    Great tip Debra.

    This is something I use all the time for any work done for clients.
    However, I don’t use the Used Area as the range on any sheets involving Data Entry
    As you say, it is not a security feature, but I like the fact that this option prevents the users from Inserting or Deleting any rows or columns, and thereby reduces the risk of such actions ruining any formulae that have been set.

    The range that I use limits the the columns to the used columns range, but allows for additional rows to the used range to allow for further data entry.
    As such, my event code is not set at the workbook level, but at the Sheet level on the Activate action.

    Private Sub Worksheet_Activate()
    Dim myRange As Range
    Dim addRows As Long
    addRows = 200
    Set myRange = UsedRange.Resize(addRows)
    Me.ScrollArea = myRange.Address
    End Sub

    I just change the value of addRows to whatever would be appropriate for the particular sheet

    • Thanks Roger, that’s a great technique for data entry sheet!
      I guess your workbook Open code activates another sheet as a starting point, so you can ensure that the data entry sheet is activated by the user later.

  2. Roger Govier says:

    Hi Deb

    Yes, you are absolutely correct, and I should have mentioned that.
    The Workbook Open event takes the user to a control sheet called Menu, which has buttons with macros attached to take them to other sheets.
    Each individual sheet has a button called Menu, which takes them back to the Menu sheet.
    Very often, the tabs themselves are hidden, so the user has to navigate under “my” control.

  3. Oz says:

    OUTSTANDING!
    This will be immediately useful in a project I’m working on now, and in a lot of others. I built a lot of apps in Excel and it’ll be great to have Excel behave more like an app, and that means users can’t scroll around into blank space. Thanks for this.

  4. manda imms says:

    thanks Debra! been trying to figure out how to do this, but I was making it way more complicated than it needed to be. cheers!

  5. Louis says:

    Thanks Debra

    This is simple and great. I searched YouTube for this but ran into complicated formulas. This has helped me with test that I created using excel.

  6. Rachel R. says:

    I set the Scroll Area manually and it works until I close the file. Once I do that and open it again, the scroll area is going back to old times.

    Why doesn’t the range I enter stick?

  7. Roger Govier says:

    Hi Rachel
    Setting the range manually only ever persists for that session, and is always lost on closing Excel.

    You would either need to set the range with the Worksheet Activate event, similar to what I posted earlier, or place some code in the ThisWorkbook module as a Workbook Open event setting

    Sheets(yoursheetname).ScrollArea=your range

    Substitute your values as appropriate

    S

  8. Rachel R. says:

    Thanks Roger.

    The problem is that I do not see Workbook Open setting. Where can I find it?

  9. Roger Govier says:

    @Rachel

    Hi Rachel

    When you go to the ThisWorkbook module in the VB Editor, at the top row of the Code window there are 2 dropdowns.
    Select the left and choose Workbook, then in the right hand drop down you will get a list of all of the Workbook |Events.
    Scroll down until you get to Open and select it and you will get

    Private Sub Workbook_Open()

    End Sub

    then enter within the sub the required code e.g

    Private Sub Workbook_Open()
    Sheets(“Sheet1”).ScrollArea = “A1:M1000”
    End Sub

    Change the sheet name and the range to suit your requirement.

    • Lissa Davis says:

      Hi,

      That last script has worked perfectly for one of my worksheets, but how do I make it work when I have multiple sheets with different areas?

      Thanks
      Lissa

  10. Roger Govier says:

    Hi Lissa
    Take a look back at the very first comment where I show the use of the Worksheet Activate event to set the scroll area.
    Then, you can make the area different for each sheet, and as you activate the sheet, the scroll area changes to whatever you have decided is appropriate.

    If required on some sheets, you can set the scroll area to be null. “” to remove. The scroll area.

  11. Priya says:

    Hi, am trying to use the code Sheets(“Sheet1”).ScrollArea = “A1:U28” but it dsnt work.
    Followed the instructions given by you but no luck..Please help

    • Roger Govier says:

      Hi

      Where are you placing this code?
      Is it somwhere where you know it definitely gets run?

      If you are really stuck, send me a copy of your workbook

      roger at technology4u dot co dot uk

      Replace the at and dot to make a valid email address

  12. Priya says:

    Hi Roger,

    Thank you so much for the prompt reply on your blog.
    The code now works fine..I have multiple worksheets and I applied this code on the index sheet.
    For other sheets also i need similar feature.. can you please help

  13. Priya says:

    Hi,

    I have index page from which i want to access other sheets in the workbook.
    Hyperlinks are all given from the index page and i also want to hide the other sheets from the index page. How do i do this?

    When a specific link is clicked, it should take the user to the related sheet. Please help.

    Priya

  14. Roger Govier says:

    Hi Priya

    The simplest way, would just be to Hide Tabs.

    File > Options > Advanced > in the Display section, uncheck Show Sheet tabs.

    Then, navigation will only be able to take place via your hyperlinks.

    Or, via VBA code the line would be
    ActiveWindow.DisplayWorkbookTabs = False

Leave a Reply to Lissa Davis Cancel reply

Your email address will not be published. Required fields are marked *