Restrict scrollable area

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

Is there a way to restrict the area in which the user can move to?

For example, I have a spreadsheet that hs an area from A1:I76. I want
to allow the user to scroll anywhere within this area, but not be able
to go outwith this area...e.g. they cannot move to cell I92 and they
cannot scroll the window past column I and below row 76.

Is this possible?

Duncs
 
Each worksheet has a .scrollarea property that you can set. But in xl2003 and
below, excel doesn't remember it between closing and reopening (not sure about
xl2007+).

So you could use a macro that runs each time that workbook opened.


Option Explicit
Sub Auto_Open()

Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet1")

With wks
.ScrollArea = "A1:I76"
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
 
Back
Top