Scroll Area

[Paul Dusterhoft]

How do you set the Scroll Area on a worksheet so that the ability to move
around the sheet is limited to the selected area?

 

[Anne Troy]

Hide all other rows and/or columns.
************
Anne Troy
www.OfficeArticles.com

 

[Gord Dibben]

Paul

Hiding the unused rows and columns then protecting the sheet is the usual
method.

Setting the scrollarea using VBA is another method.

Note: Setting ScrollArea is good for that session only and only the
activesheet. Has to be reset next time workbook is opened.

Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
specify which worksheet.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A160"
End Sub


Gord Dibben Excel MVP

 

[Jim May]

You can also
Right-Click on your sheet-tab
Select View Code
If Properties Window is not shown press F4
In Properties Window, near bottom you'll see
Scroll Area - In blank box to right
enter desired Range, say B2:G20
Exit out of VBE
Done

 

[Gord Dibben]

Good tip Jim but this method is also lost when you close the workbook.

Has to be reset when re-opened, hence my Workbook_Open code.


Gord

 

[Jim May]

Wasn't aware of this feature (?). LOL
Thanks,
Jim

 

[Roger Govier]

Hi Paul

Right click on the worksheet Tab and choose View Code.
If the Properties window is not visible, press F4.
In Scroll Area, set the range of the area you require.

Regards

Roger Govier

 

[Jim May]

But even after saving the Workbook;
Your Scroll Area is Lost;
See Gord's reply to my suggestion above..
Jim

 

[Roger Govier]

Hi Jim

Thanks for the heads up!
I had not seen either your, or Gordon's post on my system when I posted my
response. I equally was unaware that the setting didn't "stick" on saving.

In all cases where I use the technique to prevent users accessing parts of
the sheet, I set it via code in the workbook open event, but thought that a
manual setting would also work and be a simpler answer for the OP.

Thank goodness we never stop learning!!!

Regards

Roger Govier