How do I lock a screen to prevent scrolling?

[Roger]

I have developed a model for people to use with several drop down menus. The
worksheet is locked except for variable info cells. All of the calculations
are done several rows below the usable worksheet and I am trying to lock the
screen to prevent people from scrolling down through the calculations.

Is it possible to lock the screen?

 

[kassie]

When you *lock* or protect the worksheet, you have the option to allow users
to select unlocked and locked cells. If you allow them to only select
unlocked cells, they may be able to see the results in the various cells, but
they will not be able to select these cells, and then see the formulae.

You can also freeze panes in such a way that the frozen part takes up the
entire screen , thus preventing any one from viewing anything but the range
from A1:O28. If you then strech the row heights and column widths slightly,
users will not even see row 29, or column P.

 

[MartinW]

Hi Roger,

Another option is to put your calculations on a separate
sheet and hide that sheet.You can also hide the rows in
your second sheet and then password protect the sheet
before you hide it.

HTH
Martin

 

[Gord Dibben]

Roger

One more method.

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

I have developed a model for people to use with several drop down menus. The
worksheet is locked except for variable info cells. All of the calculations
are done several rows below the usable worksheet and I am trying to lock the
screen to prevent people from scrolling down through the calculations.

Is it possible to lock the screen?

Gord Dibben MS Excel MVP

 

[ravi varma]

please let me know the steps

Roger

One more method.

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



Gord Dibben MS Excel MVP

 

[ravi varma]

Please advice the steps

Roger

One more method.

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



Gord Dibben MS Excel MVP

 

[Gord Dibben]

There are two routines presented in my original post.

Whichever one you choose to use will be placed in Thisworkbook module.

To access that module in 2003 or earlier, Alt + F11 to open the VBE

Select your workbook/project, expand then double-click on Thisworkbook
module. Copy/paste the code into that module.

Edit the range and sheet name to suit. Alt + q to return to the Excel
window.

Save the workbook.

To access in 2007, hit Developer Tab and Visual Basic.

Select your workbook/project and expand to see Thisworkbook.

Double-click to open and copy/paste into that module.

Edit to suit. Alt + q to return to the Excel window.

Save the workbook.


Gord

On Mon, 2 Mar 2009 06:25:01 -0800, ravi varma <ravi