Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent User From scrolling down (97/2K)

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent User From scrolling down (97/2K)

    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.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Prevent User From scrolling down (97/2K)

    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

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User From scrolling down (97/2K)

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User From scrolling down (97/2K)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User From scrolling down (97/2K)

    Thanks Kieran. All techniques are useful. I might need this in the future.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •