Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    ScreenArea Visibility (XL2000 SR-1)

    <font color=blue>Desired End Result:</font color=blue>
    On a screen ("main"), I want the user to be able to see the range ("A1:P19") but the only cells on the screen that could be clicked on (or selected) would be the range ("D10:J15").

    <font color=blue>What I've Tried And It Didn't Work:</font color=blue>
    ThisWorkbook
    Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Sheets("main").Select
    Range("a119").Select
    ActiveWindow.Zoom = True
    Range("a1").Select <font color=448800> 'unselects the previous range after zooming the selection.</font color=448800>
    Sheets(""main").ScrollArea = "scroll_main" <font color=448800> 'scroll_main is is range name defined as Main!D10:J15</font color=448800>
    ...

    The attempt here was to limit where the user could click (or select a cell) by using the scrollArea reference. The result of the code I used was a screen view based more on the scroll-area than the zoomed area...it was ugly. The Zoom=True works on every other sheet where I've used it, but the "main" sheet is the only one where the scrollArea did not include cell A1.

    Is there a different approach?
    - Ricky

  2. #2
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ScreenArea Visibility (XL2000 SR-1)

    First, I'm normally considered an Excel expert. But all of these postings with so much reference to VBA code has me intimidated!!

    Unless there's a real need for code -- dynamic changes, etc. -- why use it?

    For your solution: Try "unlocking" the cells that you DO want the user to be able to click on (Format>Cells>Protection), and then Protect Sheet (Tools>Protection>Protect Sheet).

    Will that work for you?

  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: ScreenArea Visibility (XL2000 SR-1)

    Ricky,

    If you lock all cells (Format Cell, Protection) except those that you allow users to select, protect the worksheet, then the following might achieve what you want :<pre>With Worksheets("main")
    .Range("A1:P19").Select
    ActiveWindow.Zoom = True
    .EnableSelection = xlUnlockedCells
    End With</pre>

    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ScreenArea Visibility (XL2000 SR-1)

    VBA allows you to do many things that you cannot do through the normal interface - display and use forms, create custom toolbars, create specific functions that are not included in the normal Excel package, and otherwise bridge the gap between what comes in the box and what you wish to create. The more you learn about VBA, the less intimidated you will feel.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ScreenArea Visibility (XL2000 SR-1)

    Richard, I agree but in this case it's fairly important that the user not be able to scroll beyond the intended visible area. For example, look at the screen in front of you now as you read this message. It would be terribly unprofessional in appearance if you were able to scroll 80 lines down to an area that looked like a spreadsheet!

    The screen in question is designed so that everything you would need to see or do is in front of you. Can't go left/right. CAn't go up/down.
    - Ricky

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ScreenArea Visibility (XL2000 SR-1)

    Attached is my 'amateur' effort, adapted from something I used a while back.
    I have turned of scroll-bars and row & column headers.
    You should not be able to select a cell beyond row 16 or column 10.

    I am sure some expert will be able to refine it - the Sub below goes in the sheet object:<pre>
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    a = ActiveCell.Address
    Col = Asc(Mid$(a, 2)) - 64 'Column number
    Row = Val(Mid$(a, 4)) 'Row number
    If Row > 16 Then Cells(16, Col).Activate
    If Col > 10 Then Cells(Row, 10).Activate
    End Sub</pre>

    Attached Files Attached Files

Posting Permissions

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