How do I lock a screen to prevent scrolling? in Excel General Questions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              Roger 11/28/2006 4:06 AM PST   Question   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?     Was this post helpful to you?         Reply |   Top           kassie 11/28/2006 5:16 AM PST   Answer   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. "Roger" wrote: > 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?     Was this post helpful to you?         Reply |   Top           MartinW 11/28/2006 5:52 AM PST   Answer   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     Was this post helpful to you?         Reply |   Top           Gord Dibben 11/28/2006 8:55 AM PST   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 On Tue, 28 Nov 2006 04:07:01 -0800, Roger <Roger@discussions.microsoft.com> wrote: >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     Was this post helpful to you?         Reply |   Top           ravi varma 3/2/2009 6:24 AM PST   please let me know the steps "Gord Dibben" wrote: > 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 > > On Tue, 28 Nov 2006 04:07:01 -0800, Roger <Roger@discussions.microsoft.com> > wrote: > > >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 >     Was this post helpful to you?         Reply |   Top           Gord Dibben 3/2/2009 8:37 AM PST   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 varma@discussions.microsoft.com> wrote: >please let me know the steps > > >"Gord Dibben" wrote: > >> 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 >> >> On Tue, 28 Nov 2006 04:07:01 -0800, Roger <Roger@discussions.microsoft.com> >> wrote: >> >> >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 >>     Was this post helpful to you?         Reply |   Top           ravi varma 3/2/2009 6:41 AM PST   Please advice the steps "Gord Dibben" wrote: > 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 > > On Tue, 28 Nov 2006 04:07:01 -0800, Roger <Roger@discussions.microsoft.com> > wrote: > > >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 >     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies