Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restricting Workspace and deavtivating menu (2002)

    Loungers - I need your assistance again. I'm building a spreadsheet for Performance Appraisals so I need to limit what some people can see and move to. I have two problems that I hope someone can help me with.

    1. I need to be able to find away to define a work space to prevent moving outside the defined range,
    2. I want to be able to deactivate/activate the Hide/Unhide sheet menu based on a password.

    Any thoughts?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Restricting Workspace and deavtivating menu (2002)

    Hi Dean,

    1: To restrict cursor movement on Sheet1 to unprotected cells, set the properties of all cells (except the ones to remain unlocked) as locked and protect the worksheet. Then add the following code to the workbook:

    Private Sub Workbook_Open()
    Worksheets(
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Workspace and deavtivating menu (2002)

    Macropod,

    Thanks very much for the info - will give it a shot.

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

    Re: Restricting Workspace and deavtivating menu (2002)

    It's not very pretty, but the code below placed into a worksheet's Object will keep the user from selecting a cell outside of a specified range; in this case A1:H15.

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If EnableSelect Then Exit Sub
    Static LastSelection As Range
    If LastSelection Is Nothing Then Set LastSelection = [A1]
    If Intersect(Target, [A1:H15]) Is Nothing Then
    LastSelection.Select
    Else
    Set LastSelection = Target
    End If
    Application.ScreenUpdating = True
    End Sub</pre>


    You can also designate a scroll area by placing this line in the ThisWorkbook object:

    <pre>Private Sub Workbook_Open()
    Sheets("sheetname").ScrollArea = "rangename"
    Application.OnKey "^{PGUP}", "" <font color=blue>'Disables Page Scrolling</font color=blue>
    Application.OnKey "^{PGDN}", "" <font color=blue>'Disables Page Scrolling</font color=blue>
    Exit Sub</pre>


    And, if you intend for the designated "workspace" to be visible on the users screen when the sheet opens, something like this can be placed in the ThisWorkbook object::

    <pre>Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMaximized
    Sheets("sheetname").Activate
    Sheets("sheetname").Select
    Range("rangename").Select '<font color=blue>or you can use "A1:H15", for example</font color=blue>
    ActiveWindow.Zoom = True
    Range("A1").Select
    End Sub</pre>

    - Ricky

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Workspace and deavtivating menu (2002)

    Tricky - thank you very much will try this as well

Posting Permissions

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