Limit Scroll Area Use Excel VBA

The following VBA code can help you to limit the scroll area for a particular worksheet.

VBA code

Excel’s ScrollArea property allows you to set the scroll area for a particular worksheet. For instance, this statement sets the scroll area on Sheet1 so the user cannot activate any cells outside of A1:G10.

Sub LimitScrollArea()
Sheets("Sheet1").ScrollArea = "A1:G10"
End Sub

If for some reason you need to clear the scroll area limits, you can remove the restriction with this statement:

ActiveSheet.ScrollArea = ""

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

  1. Activate the Visual Basic Editor by pressing ALT F11.
  2. Right-click the project/workbook name in the Project window.
  3. Choose Insert -> Module.
    Insert Module
  4. Type or paste the code in the newly created module.

Comments

  1. says

    This macro works for me. Thanks! There’s a saying about working excel numbers. “If you don’t work the numbers, the numbers will work you”

  2. says

    Works for me but there’s an important thing to note. I have a worksheet with all the rows beneath a table hidden to clean up the sheet. I couldn’t get the scroll area command to respond and what I found out was, if you have all the rows hidden beneath a table, you have to limit the scroll area to one row under your visible row. For example my last cell was in L28, so when I tried to limit the scroll area to A1:L28, it wouldn’t work. I had to use A1:L27. You can’t edit the last row anymore so you may have to unhide another row in order to make this work. Sorry for being so long about it. I hope it makes sense.

Leave a Reply

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