Announcement

Collapse
No announcement yet.

VBA Macro To Protect Sheet But Allow AutoFilter & Macros

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    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.

  • #2
    Re: Autofilter Failure On Fileopen

    Code:
    .Protect Password:="Xyz", DrawingObjects:=True, _ 
            contents:=True, Scenarios:=True, AllowFiltering:=True,_ 
            userinterfaceonly:=True
    AAE
    ----------------------------------------------------

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

    Comment


    • #3
      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

      Comment


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

        See UserInterfaceOnly

        Comment


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

          Dave: Thanks for the solution.

          Cyrus Patel

          Comment

          Working...
          X