Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFilters don't work when Sheet is Protected (Excel 97)

    I have a workbook that needs to be protected, has autofilters, a macro is used to add more rows to the sheet. I don't want the users to do anything that is not in a button/macro. But, autofilters just don't work when a sheet is protected.

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    That's true, autofilers (and several other features like tracing) do not work on protected sheets. Since you're doing work behind the scene for the user, you can unprotect the sheet, do the filter, then reprotect it. I do this often myself.

    Deb <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  3. #3
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    Thanks, Deb. Unfortunately, it is the users who need to use the autofilters AND need the protection. I guess I was looking for a way to perform the autofilters AND still have protection!
    --cat

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    The following code should help you. It came from Cri and Hans Pottel at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=9184&page=&view=& sb=&o=&vc=1>This Thread</A>

    Place the following in the ThisWorkbook object (not in a general module) :

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

    If you have difficulty getting the ThisWorkBook object try :

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

    Before putiing the protection in place, you need to have the AutiFilter option switched on, i.e. the little dropdown buttons should be in place.

    Hope it assists

    Andrew C

  5. #5
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    Thanks -- that works great!
    --cat

  6. #6
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    Sorry, if I am a bother. I am new to this lounge.
    The code you sent worked great for one sheet. I need to do have filters work and protection on for 12 worksheets in the book. (There are other worksheets in the workbook that don't need this)
    Thanks
    --cat

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoFilters don't work when Sheet is Protected (Excel 97)

    You can use the following code in each sheet :

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

    Right click on the Sheet Tab, select View Code and paste the above into the code window. Repeat for each sheet you wish to use it for.

    You can replace the earlier code with :-

    Sub Auto_Open()
    Sheets(1).Activate
    End Sub

    Andrew C

Posting Permissions

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