Solved

Excel VBA -- Set multiple scroll areas

Posted on 2014-10-29
1
1,284 Views
Last Modified: 2014-10-29
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
Comment
Question by:ExpExchHelp
1 Comment
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40411097
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 41
Excel Cell Total 3 21
Excel Charts: How is this Chart made? 8 29
Copying from excel I am getting extra text 11 43
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question