How can I stop and excel worksheet from scrolling 4

[timconway]

I have created a form in a spreadsheet. I would like to keep the user from being able to scroll down the sheet. Is there a way to lock the sheet, so the user sees only what is on the screen?
The form refrences cells farther down on the sheet, but does not need to be accessed by the user.
Any help will be appreciated.

 

[carrr]

Go to Tools, Options, and uncheck Horizontal or Vertical Toolbar, or both.

 

[carrr]

You should also hide all rows/cells that are referenced for good measure. For that matter, select the cells that you want the user to be able to type in and right click, selecting Format Cells, Protection, and uncheck locked.
Now, protect the sheet and save. Your referenced data will be hidden and your users restricted to only certain cells.

 

[xlhelp]

Removing scroll bars will still let you scroll the worksheet.

You may want to use a macro like

Sub SetScrollArea()
Sheets("Sheet1&quot.ScrollArea = "A1:H30"
End Sub

 

[carrr]

xlhelp,

Very nice, thanks.

 

[timconway]

The suggestions worked great!
Thanks, Tim

 

[DeLaMartre]

Great tip, Xlhelp! Thanks! -Bob in California

 

[mudstuffin]

Just to add to xlhelp's post, use:


Sub EnableScrollArea()
Sheets("Sheet1&quot.ScrollArea = ""
End Sub


to restore the scrolling, maybe for the purposes of allowing restricted access to the rest of the sheet.



mudstuffin

 

[xlbo]

Further point - the scrollarea property must be set EVERY TIME THE WORKBOOK IS OPENED - it is not saved as part of the properties of the worksheet so you must have it as part of the workbook_open or sheet_activate event Rgds
~Geoff~

 

[xlhelp]

You could set the scroll area as an Autpen sub as well. That way, scroll area will be locked every time the file is opened.

The you could have mudstuffin's scroll area cancelling macro just in case you needed to scroll.