Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2011
    Location
    Nebraska
    Posts
    82
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Protect part of a worksheet - which checkboxes to allow all users to sort?

    This is actually a 2-part question:

    (1) I have a user in Finance with a worksheet on a network drive. Currently protected for just their dept. They'd like another department to not only view it, but be able to sort the sheet using any column, but not be able to save those changes.

    I know how to protect all or part of a worksheet, but am not familiar with the impact of the choices under "Protect Worksheet". I've also thought of using the file permissions, but I need to know it will work, rather than think it will work.

    I'm not sure how this can be done, so I thought I'd ask here.

    (2) The same user would like to allow users in the other dept. to add comments in a column - and no where else. I first ghought of suggesting Comments, but I don't know how to allow those but restrict everything else.

    Many thanks in advance!
    Mike W.
    Location: A Great Plains State

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Which version of Excel?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I will try to answer your questions, but they all seem to involve different solutions...
    (1) I have a user in Finance with a worksheet on a network drive. Currently protected for just their dept. They'd like another department to not only view it, but be able to sort the sheet using any column, but not be able to save those changes.
    This seems to be 2 separate issues. What doe you mean by the workbook is "Currently protected for just their dept"? Is some type of network access/protection (unrelated to excel) or protected in excel in the workbook/worksheet level? The only one that should affect sorting is excel worksheet protection.

    Sorting can be explicitly allowed with worksheet protection by checking that option when the worksheet is being protected. Unfortunately it only allows sorting of unlocked cells. This may not be an issue if the formulas used are based on the cell contents of the unlocked cells, but it would be a problem if the values are not based on this sorted data (you would also want all the unlocked cells together to sort them together). If you need sorting of other cells, the cells must be unlocked prior to protecting the worksheet or you must use a macro to unprotect the sheet, sort and then reprotect the worksheet. [You may want several buttons to have routines to sort particular columns if desired, for example a button in the header to sort that column]

    To not allowing saving any changes is unrelated to protection. The easiest way to do this (though not foolproof - since fools are very ingenious and it is of little help if the person wants to save the changes) is to require a password to modify the file [file save as - tools - general options, gives a dialog for a password to open and to modify the file]. You don't need one to open, but if you add one to modify and it is not entered the file is opened read-only and can not be saved [but it can be saved as a different name and then that file may be copied over the source if you do want to save the changes which would bypass what you desire...]

    I know how to protect all or part of a worksheet, but am not familiar with the impact of the choices under "Protect Worksheet".
    A worksheet is wholly protected or not protected. You can lock and unlock individual cells. Only unLocked cells can be edited when the worksheet is protected.

    (2) The same user would like to allow users in the other dept. to add comments in a column - and no where else. I first ghought of suggesting Comments, but I don't know how to allow those but restrict everything else.
    Restricting comments is not simple. Protecting a worksheet will prevent the addition of comments in all cells of the worksheet. Protecting the worksheet but checking "edit objects" will allow comments to be added, but they can be added to all cells (both locked and unlocked). You could restrict comments being added to only unlocked cells if you uncheck "select locked cells" when protecting, since this will restrict the user from only being able to select unlocked cells, so only they will get comments. But this will then prevent selecting much of the worksheet and still allow adding comments to other cells.

    You could protect without checking "edit objects" and create a macro to add comments but this could get cumbersome.
    The easiest way to restrict use in this manner is to restrict as much as possible and do all editing via dialogs, but if you really want this control, I think Access is more setup to do this, Excel is not really built to be so restrictive.

    Steve

  4. #4
    Star Lounger
    Join Date
    Jun 2011
    Location
    Nebraska
    Posts
    82
    Thanks
    9
    Thanked 0 Times in 0 Posts
    2007
    Mike W.
    Location: A Great Plains State

  5. #5
    Star Lounger
    Join Date
    Jun 2011
    Location
    Nebraska
    Posts
    82
    Thanks
    9
    Thanked 0 Times in 0 Posts
    sdckapr,
    Thanks for the detailed answer. Right now the 'owning' dept has it on a local computer and emails copies around. Any changes are 'fat-fingered' in to the 'master.' They want to move it to a network share, but want to do it smart. They sent in a ticket to our help desk and I got it. Not being real experienced, I'm asking around. You've given me much to think of. Thanks!
    Mike W.
    Location: A Great Plains State

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mike,

    I used to have a customer who redefined the term "FAT FINGER" he could screw up anything that wasn't tied down as tight as possible, I even disabled the Big Red X and set it so he couldn't load a second copy of the workbook. What I did is what Steve recommended. Every cell was locked and the worksheets protected. I created a custom toolbar, hiding all the standard toolbars so all he had access to was the functions on his toolbar. These included, data input/update/delete, sorting {four options}, reports, and exit {yes he couldn't get out except via the menus! Things were unlocked by the code and only stayed that way long enough to make the changes requested then relocked tight as a drum. It is a lot of work to set up but in the end we were both happier, me because I didn't get calls on nights and weekends when he liked to work, and him because he didn't have to pay off hour support rates!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Star Lounger
    Join Date
    Jun 2011
    Location
    Nebraska
    Posts
    82
    Thanks
    9
    Thanked 0 Times in 0 Posts
    RetiredGeek - thanks!
    Mike W.
    Location: A Great Plains State

Tags for this Thread

Posting Permissions

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