Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Excel 2002 Protect Sheet & Use AutoFilter Option

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Wisconsin - USA
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Has anyone come up with a way to code up this functionality to work in Excel 97?
    Essentially, I need to protect a sheet, but allow AutoFilter to be used for viewing purposes. In Excel 97 this is not permitted (even if the columns you want to AutoFilter on are NOT locked...).

    See this site for what it is supposed to do in Excel 2002 - "http://support.microsoft.com/default.aspx?scid=kb;EN-US;q289269"

    I'll appreciate the help...

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Turbotoan

    The only way around this I have found is to provide 2 macro buttons on a part of the sheet which will not get hidden when lines are filtered.
    Name on Button 1 = "Filter by entry in D1"
    Name on Button 2 = "Show all Data"
    Macro1 Code:
    On Error Resume Next
    ActiveSheet.Unprotect("yourpassword")
    Selection.AutoFilter Field:=4, Criteria1:=Range("D1").Value, Operator:=xlAnd
    ActiveSheet.Protect("yourpassword")
    End Sub

    Note: Here I am using Field 4 and picking up the criterial value from cell D1 (change to suit your circumstances)

    Macro2 Code:
    On Error Resume Next
    ActiveSheet.Unprotect("yourpassword")
    ActiveSheet.ShowAllData
    ActiveSheet.Protect("yourpassword")
    End Sub

    You will have to provide some instructions to get your user to type into D1 (or your chosen cell) the criteria to filter by. Or perhaps you could do it with an input box?

    Good luck
    Derek

  3. #3
    Guest

    Default

    Try this, I got this from Mr Excel (can't remember who) and it worked for me.

    Sub protect1()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

    End Sub

    Lisa

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's neat Lisa, that will help me too, thanks
    Derek

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Wisconsin - USA
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Lisa,
    That works wonderfully!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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