Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forbid changing cells (2k)

    Should know this, but don't: how can I password-protect a file to allow a user to do almost anything (set print areas, colour cells, widen columns, hide rows, etc) with a workbook but not delate or change the contents of any cells, nor put stuff into 'blank' cells?
    Sophistication of users = low, I just want to make it awkward.
    Thanks

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

    Re: Forbid changing cells (2k)

    I don't think that's easy in Excel 2000. In Excel 2002 and later, you have much more control over what users are allowed to do in a protected sheet.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forbid changing cells (2k)

    Thanks for that, Hans.
    OK anyone, suggestions for the nearest approach to what I'd like to achieve, of which the most fundamental is to forbid changing the values or formulas in cells?

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

    Re: Forbid changing cells (2k)

    Protecting a sheet in Excel is a two-step process.

    1) Unlock the cells that the users should be able to edit.
    - Select the cells that the users should be able to edit.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.
    (If you don't want users to be able to edit any cells, you can omit this step)

    2) Protect the sheet.
    - Select Tools | Protection | Protect Sheet...
    - Make sure that the Contents check box is ticked.
    - Specify a password.
    - Click OK. You'll have to enter the password again as confirmation.

    In Excel 2000, users won't be able to format cells etc. in a protected sheet.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forbid changing cells (2k)

    OK and thanks, Hans.
    Might need to persuade grateful bosses to uprate to eg xl2003 for some approach to what we want, then.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Forbid changing cells (2k)

    You can get close to what you want with code, but it will be a bit clunky and of course if they disable macros, they will be able to do what they like!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forbid changing cells (2k)

    Thanks, Rory, I'd figured that and in any event the default installation is 'macros disabled' and I suspect the punters concerned wouldn't know how to turn them on. anyway.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forbid changing cells (2k)

    If you can get the users to set the security to Medium; the following code in the Workbook module should do the trick. <pre>Private Sub Workbook_SheetChange( _
    ByVal sh As Object, _
    ByVal Target As Range)
    Dim i As Long
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End Sub
    </pre>

    Regards
    Don

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

    Re: Forbid changing cells (2k)

    As Rory pointed out, if a user clicks "Disable macros" when opening the workbook, it'll be unprotected. You could protect all sheets, and unprotect them in the Workbook_Open event - that way, the sheets will remain protected if the user disables macros. You'd also have to cancel saves in the Workbook_BeforeSave event.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forbid changing cells (2k)

    How about a splash sheet that will be the only sheet not "very hidden" which tells the user how to set the security to Medium before closing and re-opening the file.

    On workbook open, the splash screen will be hidden and all other sheets made visible through code.

    On Closing all sheets will be made "very hidden" and the splash sheet made visible through code.

    <hr>You'd also have to cancel saves in the Workbook_BeforeSave event.<hr>
    It took me a moment to grasp that one. Good point.
    Regards
    Don

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

    Re: Forbid changing cells (2k)

    Combining all these steps, it's possible to protect a workbook to a large extent. But a user with malicious intent will still be able to bypass the protection.

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forbid changing cells (2k)

    Agreed; but recall that John said:
    <hr>I just want to make it awkward<hr>
    Regards
    Don

  13. #13
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forbid changing cells (2k)

    Thanks v much, everyone, for all this. I didn't realise that I'd provoke such an interesting thread!
    I was a bit iffy about anything involving ACTION on the part of the user in order to achieve the desired outcome (ie, user does nothing special, then he can wreck the data **grin**) but the workarounds in the last few responses seem to be the way forward. I'll do that. On a copy of the workbook, to start with!
    Thanks again.

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

    Re: Forbid changing cells (2k)

    I don't know about anyone else, but I'm intrigued to know why you would want to allow users to muck about with a worksheet, but not change the data....? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  15. #15
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forbid changing cells (2k)

    I don't mind their applying and removing filters, widening, narrowing, colouring, formatting or hiding rows or columns, setting up print area(s), creating pivot tables (some chance!) - whatever they want, provided they don't mess with the data (and then send it back without their having said so!!!!)

Posting Permissions

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