Restrictions on worksheet display

[Guest]

Is it possible to lock a
worksheet so that

a) the user cannot move the cursor outside of a certain row range and a
certain column range ?

b) the use cannot change the location on the screen of the window displaying
the worksheet, and he cannot change the size of the window displaying the
worksheet?

Thanks

 

[Gord Dibben]

You can protect the workbook windows and structure under
Tools>Protection>Protect Workbook to disallow resizing or moving.

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


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Gord. The stuff about scroll area was very helpful. Now suppose I
want to change back so the user can access all the cells of the sheet. What
would the code be for that ?

 

[Gord Dibben]

Assign this to a button or shortcut key.

Sub Scroll_All()
Sheets("YourSheet").ScrollArea = ""
End Sub

Note: scrollarea will be re-set when you next open the workbook.


Gord