Prevent User From scrolling down (97/2K)

Topic

New Reply

I have a protected worksheet with selection limited to a few cells. Using Tools/Options I have turned off scrollbars. My problem is that a user can select a cell, and by continued pressing of the enter key, effectively scroll down the worksheet. Is there a way to disable this feature thus limiting the user view to the initial sheet view?
I hope that makes sense-I’m in a big rush.

Reply | Quote

Replies

You can set the EnableSelection property of the worksheet to xlUnlockedCells. Users can only select unlocked cells then. However, this setting is not saved with the workbook, so you must set it each time the workbook is opened. Put the following macro in the module behind ThisWorkbook:

Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
End Sub

Replace Sheet1 by the name of the protected worksheet.

Note: the user can get round this by disabling macros when opening the workbook.

Alternatively, you could hide all rows and columns the user doesn’t need to see. This way, it looks to the user as if the worksheet contains only a small number of rows and columns.

Reply | Quote

Each sheet has a ScrollArea property, but like Hans’s Enableselection it needs to set each time a Workbook is opened as it’s state is not saved with the workbook.

To prevent a user scrolling past row 40 or column M on sheet1, you can use (in the Workbook_Open event)

Sheets(“Sheet1”).ScrollArea = “A1:M40”

Andrew C

Reply | Quote

Many thanks, Andrew and Hans.
My memory isn’t what it used to be and I find I’m scratching my head about things that ought to be at my finger tips. I must create an indexed file containing all of the snippets of code, inter alia, that your good selves and other loungers have provided.

Reply | Quote

This is not as elegant as the methods mentioned previously, but you could use the camera tool to obtain an image of the cells you want to display.
Paste it onto a new sheet, and then hide & protect the source sheet(s).

It has the disadvantage that the displayed sheet will be an image, so the normal cell actions of copy paste etc will not operate.

Reply | Quote

Thanks Kieran. All techniques are useful. I might need this in the future.

Reply | Quote