Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA Macro To Protect Sheet But Allow AutoFilter & Macros

  1. #1
    Join Date
    7th March 2009
    Posts
    9

    VBA Macro To Protect Sheet But Allow AutoFilter & Macros

    I have a file that has an autofilter set to a specific data range.

    I have then protected the worksheet using the following code:

    VB:
    Sub SheetProtect() 
        Dim msheet 
        msheet = ActiveSheet.Name 
        With Worksheets(msheet) 
            .Protect Password:="Xyz", DrawingObjects:=True, _ 
            contents:=True, Scenarios:=True, _ 
            userinterfaceonly:=True 
             
            .EnableAutoFilter = True 
        End With 
    End Sub 
    
    
    Everything works fine.

    However, after I save the file and exit and then reload the file, the autofilter fails. The autofilter drop down arrows are still there but the feature appears frozen. The rest of the worksheet works normally.

    If I unprotect the worksheet manually and protect it again using the above code, it works normally.

    Any insight into this situation would be appreciated.

    Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Autofilter Failure On Fileopen

    VB:
    .Protect Password:="Xyz", DrawingObjects:=True, _ 
    contents:=True, Scenarios:=True, [B][COLOR="Red"]AllowFiltering:=True,[/COLOR][/B]_ 
    userinterfaceonly:=True 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    7th March 2009
    Posts
    9

    Re: VBA Macro To Protect Sheet But Allow AutoFilter & Macros

    Thanks AAE - your suggestion works. However, I have encountered a new problem:

    When I open the file and try the auto filter manually, it works perfectly; I can even record the autofilter process as a macro. But when I try to run the same autofilter via the vba code, I get the error message:

    Run time error 1004 - You cannot use this command on a protected sheet.

    So while the autofilter actions are allowed manually, the macro execution fails.

    Any advise will be greatly appreciated.

    Thanks

    Cyrus Patel

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    7th March 2009
    Posts
    9

    Re: VBA Macro To Protect Sheet But Allow AutoFilter & Macros

    Dave: Thanks for the solution.

    Cyrus Patel

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro To Protect All But One Sheet
    By TheDude in forum EXCEL HELP
    Replies: 12
    Last Post: August 22nd, 2008, 08:11
  2. Skip Worksheets In Sheet Protect Macro
    By nightkil in forum EXCEL HELP
    Replies: 6
    Last Post: January 6th, 2007, 09:26
  3. protect sheet with macro buttons
    By [the blue fox] in forum EXCEL HELP
    Replies: 2
    Last Post: May 9th, 2006, 12:40
  4. Protect Sheet in Macro
    By leethebee in forum EXCEL HELP
    Replies: 8
    Last Post: April 5th, 2006, 04:07
  5. Replies: 4
    Last Post: July 22nd, 2003, 02:04

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
  •  
porno