ScrollArea

[Kevin]

Dear NG:

I'm somewhat new to spreadsheets and VB so please bear with me.

I wanted to restrict the scroll area of sheet01 (since it only contains a
graph) to A1:Q26.

If I open the Visual Basic Editor and enter A1:Q26 in ScrollArea of the
Properties Window,
the scroll area is restricted as expected. However, saving, closing and
reopening Excel erases
the entry in VB's ScrollArea Properties Window.

I've entered Sheet01.ScrollArea = "A1:Q26" into the sub Private Sub
Worksheet_SelectionChange.
which seems to work OK.

Is this the preferred or proper way to go about what I'm trying to
accomplish.

Thanks for any insights.

-Kevin

 

[Gord Dibben]

You can set the ScrollArea so users cannot move out of that area.

Since the scrollarea method does not stick between sessions you will have to
reset it each time.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:Q26"
End Sub

Or also in the Thisworkbook module to limit scrollarea on all sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveSheet
.ScrollArea = "A1:Q26"
End With
End Sub

Right-click on the Excel Icon left of "File" on the menu bar and slect "View
Code"

Copy/paste your choice of subs from above into that module.

Save the workbook and Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Kevin]

Gord Dibben,

Thanks. I had a feeling that there was a better place for me to be entering
the code.

Thanks again.

-Kevin