+ Reply to Thread
Results 1 to 5 of 5

Enabling filter on a protected sheet

  1. #1
    Registered User
    Join Date
    08-12-2015
    Location
    Bedfordshire, England
    MS-Off Ver
    2010
    Posts
    5

    Enabling filter on a protected sheet

    Hello
    I am having trouble enabling filtering on a protected worksheet.

    I have already enabled the +/- with the code listed below in VBA. It seems to be this that is causing the problem. When initially protecting the sheet I select ‘use Auto filter’ and I can sort using the filter buttons until the spreadsheet is closed and saved then it does not retain this information and I can no longer sort using the filter buttons i.e. if you unprotect and protect again excel usually remember what was ticked and the ‘use Auto filter’ is not selected.

    I have tried protecting the sheet with the ‘Use Auto filter’ selected without the code in VBA and it works fine so it must be something to do with this. Is there an additional code I can add to VB to enable both functions.

    Thanks in advance for your help

    Kerry

    _________________________________________________________________________
    Option Explicit

    Private Sub Workbook_Open()
    Dim wsSheet1 As Worksheet

    Set wsSheet1 = Me.Worksheets(1)

    With wsSheet1
    .EnableOutlining = True
    .Protect Contents:=True, UserInterfaceOnly:=True
    End With

    End Sub
    _____________________________________________________________

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,027

    Re: Enabling filter on a protected sheet

    Try something like this:
    Please Login or Register  to view this content.
    This will protect your sheet with the password "mypassword" when the workbook is opened. Change the password to suit your needs. If you currently have code in a macro to protect and unprotect the sheet, remove that code. You don't need it since the sheet is protected on opening the workbook. Also make sure that you click the "Data" tab in your top menu and then click the "Filter" button to make the little filter arrows visible in your header row. These arrows must be visible before you save your file so that they remain visible on opening.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    08-12-2015
    Location
    Bedfordshire, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Enabling filter on a protected sheet

    Thanks for your help. I have changed the code, I can now sort using the filter buttons but I can no longer use the +/- buttons (Grouped columns)
    I need a code that will allow both functions if possible

  4. #4
    Registered User
    Join Date
    08-12-2015
    Location
    Bedfordshire, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Enabling filter on a protected sheet

    Actually, it does now work, I juggled some of the codes. For info the code now reads as below. Thanks very much for your help
    Option Explicit

    Private Sub Workbook_Open()
    Dim wsSheet1 As Worksheet

    Set wsSheet1 = Me.Worksheets(1)

    With wsSheet1
    .EnableOutlining = True
    .Protect Contents:=True, UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
    End With

    End Sub

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,027

    Re: Enabling filter on a protected sheet

    Glad it worked out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Enabling hide/unhide rows in protected sheet
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2014, 05:16 AM
  2. Enabling Outlining (Groups) in a Protected Sheet
    By anguspgreenwood in forum Excel General
    Replies: 9
    Last Post: 01-20-2014, 10:02 AM
  3. enabling paste in protected sheet
    By cherryt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-09-2013, 05:42 AM
  4. [SOLVED] Enabling AutoFilter & Outlining in protected sheets
    By makku in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-16-2012, 06:15 AM
  5. Enabling Grouping / Outline On A Protected Sheet VBA
    By SamuelT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2009, 06:21 AM
  6. Enabling Auto Filter when sheet is protected
    By nadiaz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2007, 10:47 AM
  7. Enabling macros while the sheet being protected !
    By dinesh in forum Excel General
    Replies: 13
    Last Post: 09-27-2005, 03:47 PM

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