Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1665
  • Last Modified:

Excel VBA -- Set multiple scroll areas

In Excel, I'm using VBA (see below) to limit the scroll area upon opening a spreadsheet.

Private Sub Workbook_Open()

    'Locks Scroll Area
    Sheet1.ScrollArea = "C14:D20"
    
End Sub

Open in new window


This solution works great except that I need several (non-adjacent) cell ranges.    Unless not possible otherwise, I'd prefer to NOT use the "Lock cell/sheet" feature.

I attempted to tweak the code (see below); however, it throws an error message.   How can I use multiple cell ranges as part of the VBA below?

Private Sub Workbook_Open()

    'Locks Scroll Area
    Sheet1.ScrollArea = "C14:D20, D23, B28"
    
End Sub

Open in new window

0
ExpExchHelp
Asked:
ExpExchHelp
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
The scroll area must be a contiguous range (i.e., all cells defined in a single, rectangular area with no gaps).  You can't use multiple ranges.

Instead, you may wish to protect the sheet and limit user selection to just the unlocked cells.  The behavior will be the same; users will only be able to select cells, either through mouse-click or [Tab] or arrow movement.
 protect
Regards,
-Glenn
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now