Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Locking cells

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

    Default

    I've got a spreadsheet where I have changed the EnableSelection to 1 - xlUnlockedCells so the users can't go to cells I have locked. When I save the sheet and re-open it, the EnableSelection is back to 0 -xlNoRestrictions

    How do I keep the users from being able to go to a locked cell. When I protect they can't modify, but they can still go to it.

    I am making any sence?

    Thanks;

    edit- It's Office 2000 Small Business edition

    [ This Message was edited by: hviking on 2002-04-03 12:43 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried using the Tools | Protection... menu command?

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

    Default

    Yes. That keeps them from typing in the selected cell, but I want them to not be able to even go to that cell.

    In Office XP this is very simple, just Protect and save, but I can't figure it out in Office 2000

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unfortunatly this sheet function does not
    hold it's value when closed. You must set
    it back upon openning via VBA code.

    eg

    Private Sub Workbook_Open()
    Sheet1.EnableSelection = xlUnlockedCells
    End Sub




    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's an alternative.

    Right-click on your sheet tab, left click on View Code, and paste this in:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:D10")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
    Range("A1:D10").Select
    ActiveCell.Select
    Application.EnableEvents = True
    End If
    End Sub

    Please note its features and limitations:

    Cannot select outside of range.
    Allows you to copy a range from within the acceptable range to outside the range, if the active cell of the copy range is within the acceptable range.
    Allows you to drag from within the range to outside of the range.
    Allows for spell check change, and for formatting change only after spell check change.

    If you want your acceptable range to always be highlighted (selected), delete the line
    ActiveCell.Select

    Any help?

    Tom Urtis


    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-04-04 00:04 ]

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 22:53, Tom Urtis wrote:
    Here's an alternative.

    Right-click on your sheet tab, left click on View Code, and paste this in:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:D10")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
    Range("A1:D10").Select
    ActiveCell.Select
    Application.EnableEvents = True
    End If
    End Sub

    Please note its features and limitations:

    Cannot select outside of range.
    Allows you to copy a range from within the acceptable range to outside the range, if the active cell of the copy range is within the acceptable range.
    Allows you to drag from within the range to outside of the range.
    Allows for spell check change, and for formatting change only after spell check change.

    If you want your acceptable range to always be highlighted (selected), delete the line
    ActiveCell.Select

    Any help?

    Tom Urtis


    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-04-04 00:04 ]
    Welcome back Tom
    Where have you been ??

    Long time no post.
    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ivan, thanks, I wish I could spend more time on this board!! Lately my business has really picked up and I've had several Excel and property management contracts keeping me occupied.

    Still working on my web site...too bad there isn't VBA code to create >24 hours per day.

Some videos you may like

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
  •