+ Reply to Thread
Results 1 to 3 of 3

filtering and sorting in protected sheets

  1. #1
    Christian Galbavy
    Guest

    filtering and sorting in protected sheets

    Hy!

    I have the following problem:
    I have a sheet with some locked cells, the sheet is protected, but I have
    selected, that filtering and sorting (and also cell manipulation) should be
    allowed. But the AutoFilter can not be activated, and when I try to sort the
    cells, I get the message that the cells are locked and sorting is not
    possible.
    I do not understand this problem.

    Thanks for any information.
    Regards
    Christian



  2. #2
    Dave Peterson
    Guest

    Re: filtering and sorting in protected sheets

    xl2002 added some options to allow filtering and sorting of a protected
    worksheet.

    Until then, I think you'd have to give the user a way to sort the data--maybe a
    macro that would unprotect the sheet, sort the data and then reprotect the
    sheet.

    But for the autofilter, you can protect the worksheet in code (if the autofilter
    is already applied):

    Option Explicit
    Sub auto_open()
    With Worksheets("sheet1")
    .Protect Password:="hi", userinterfaceonly:=True
    .EnableAutoFilter = True
    End With
    End Sub

    It needs to be reset each time you open the workbook. (excel doesn't remember
    it after closing the workbook.)

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Christian Galbavy wrote:
    >
    > Hy!
    >
    > I have the following problem:
    > I have a sheet with some locked cells, the sheet is protected, but I have
    > selected, that filtering and sorting (and also cell manipulation) should be
    > allowed. But the AutoFilter can not be activated, and when I try to sort the
    > cells, I get the message that the cells are locked and sorting is not
    > possible.
    > I do not understand this problem.
    >
    > Thanks for any information.
    > Regards
    > Christian


    --

    Dave Peterson

  3. #3
    Mladen_Dj
    Guest

    Re: filtering and sorting in protected sheets


    "Christian Galbavy" <slater2000@gmx.at> wrote in message
    news:%23LtlRMV2FHA.896@TK2MSFTNGP09.phx.gbl...

    > cells, I get the message that the cells are locked and sorting is not
    > possible.


    You can't perform sort on locked cells when sheet is protected. You must
    unlock all cells in the range. If you don't want user can change cells then
    uncheck "Select unprotected cells" in Protect sheet dialog box. User will
    still be able to filter and sort table, but could not be able to
    select/change cells. I play with this options, and find one weaknes of this
    method. User is able to navigate in worksheet by using tab button, and
    because cells are unlocked can delete content of cells but is unable to
    write to cells. Its really strange to me???



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1