Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering a protected sheet

    Is there a possibility to use the filter on a protected sheet?

  2. #2
    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: Filtering a protected sheet

    Hi,
    Not as far as I know - you would need to unprotect it, filter it and then protect it again. You could do that with VBA but you'd need to store the password somewhere (either within the code or on a hidden sheet I guess)
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Hans,

    You can copy data from a protected sheet onto an unprotected sheet and filter it there- would that help?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Hi all,

    I need to be a little bit more explicit. I made a spreadsheet containing a database full of information that is used by other people. They are not allowed to change anything on the spreadsheet, so I protected it. But, it would be very handy if they could still use the filter to select more precise information from that database and then print the selection. Unfortunately, when the worksheet is protected, filtering is no longer available. Is there a way to overrrule that, eventually using vba?

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    You can filter on a protected sheet without using VBA by using an advanced filter. I just did a bit of a test and it seemed to work ok. You need to set the criteria for the filter in a range, but it doesn't have to be on the same sheet.

    Look at Filters,advanced under the online help.

    Jon

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Put this code on ThisWorkbook level of your workbook and adapt MySheetName to your database worksheet. If you need assistance for this, or have to handle the whole workbook, please ask:


    Private Sub Workbook_Open()
    'Make sure that Grouping and Autofilter in MySheetName are operational/reset
    With Worksheets("MySheetName")
    .EnableOutlining = True
    .Protect contents:=True, userInterfaceOnly:=True
    .EnableAutoFilter = True
    'If you want to expand the autofilter when starting, adapt and activate next line
    '.Range("PutName_OR_YourRange").AutoFilter Field:=1
    MsgBox "Autofilter and Grouping in " & .Name & " now operational"

    End With
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Many thanks to all of you.
    In the meantime, I've got another interesting solution for my problem from a different forum (author = Tom Ogilvy, Excel MVP), which I want to share with you:

    If the filter is already applied - the dropdown arrows are already visible, then this code will allow you to use it

    Sub AllowFilter()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect UserInterFaceOnly := True
    End Sub

    This would have to run at least each time the workbook was opened as these settings are not saved with the workbook. Assumes Excel 97 or later.

    If you put this code under the WorkBook_Open event of ThisWorkBook, then this should do it.

    Hans

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Hans Pottel, _another_ solution ? See the one I posted. If you do not have Grouping then delete these lines.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a protected sheet

    Sorry cri,

    didn't realize that it is essentially the same code. Thanks again for your help.

Posting Permissions

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