Preventing scroll outside active area

B

Ben

Is there a way of preventing a user from scrolling left and right outside of
the screen area WITHOUT resorting to hiding the colums on either side.

Thank you
 
D

Don Guillett

from vba help index for scroll and you would see scroolarea directly
underneath. Must be set from a workbook_open event each time workbook is
opened.
ScrollArea Property
See Also Applies To Example Specifics
Returns or sets the range where scrolling is allowed, as an A1-style range
reference. Cells outside the scroll area cannot be selected. Read/write
String.

Remarks
Set this property to the empty string ("") to enable cell selection for the
entire sheet.

Example
This example sets the scroll area for worksheet one.

Worksheets(1).ScrollArea = "a1:f10"
 
G

Gord Dibben

Ben

Through VBA

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:T30"
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:T30"
End With
End Sub


Gord Dibben MS Excel MVP

Is there a way of preventing a user from scrolling left and right outside of
the screen area WITHOUT resorting to hiding the colums on either side.

Thank you
Click to expand...

Gord Dibben MS Excel MVP
 
Top