how to stop the cursor scrolling
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: how to stop the cursor scrolling

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a an excel spreadsheet which only has cols A - E that I want visible to the user. Cols G - L are used for off-screen calculations and reporting etc. So I have reduced the window size of the spreadsheet to only show cols A - E and applied Workbook and sheet protection with a password with all options ticked. This locks the window size ok and stops the user from viewing formulas etc.

    However .. if they scroll right with the cursor .. the spreadsheet moves to the right to show my reporting colums ( which I need to leave un-hidden cos I use them as a print Preview )

    How do I make the cursor movement restrict to columns A - E only ?

    Thanks

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bill


    Right click on the sheet name tab, select "View Code" an place in this

    Private Sub Worksheet_Activate()
    Me.ScrollArea = "A1:E100"
    'Set back to normal
    'Me.ScrollArea = ""
    End Sub


    You could also just hide all the columns Except A:E



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave thanks .. I just tried that .. but it still allows me to scroll to col F onwards ?

    I saved/exited .. is there something else I need to do ?

    Thanks for your help
    Bill

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bill

    All you need to do is select any other sheet and then select the original sheet and the code will take effect.



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    great Dave .. that works .. cheers

    just one more question if I can .. how do I make Excel do this little "toggle" between the 2 sheets each time it opens to activate this ?

    I notice that I have to do it manually and if a users doesnt do it then the "scroll lock" doesnt kick in.

    cheers Bill

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bill


    While in Excel, Right click on the Excel symbol, top left next to "File" and select "View Code" then paste this code in.

    Private Sub Workbook_Open()
    Sheets("TheSheet").Activate
    End Sub

    Where "TheSheet" is the name of the sheet housing your code.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave .. excellent .. works a dream .. thanks very much for your help .. appreciate it.

    Bill

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    to add:

    Nerer to question my old pal Dave Hawley, some options i feel.

    In VBE can set SCROLL area locking cells, this will work fine as you req, saves VBA

    Alos set you works "reporting" data in a diff sheet and hide, also delete the non reqd cells by row and col this will TOTALLY remove them if say F:IV deleted and 100:65536 deleted ...

    this will effect the scroll areas.

    Just some options NON VBA. I will add that code Dave has provide is perfection ! (cheers Dave)

    PS Dave if you see this i need you 6 mate!
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com