Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: autofilter & protection

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was wondering if there was anyway to protect a sheet, and still be able to use the autofilter function. Thanks Dan

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    not sure if this is suitable, but you could protect your sheet and use another sheet with =Sheet1!$A$1 refs for the autofilter. you could also use an array (eg. =Sheet1!$A$1:$A$20 & ctrl+shift+enter) for the second sheet, which you can still autofilter but the user can't for instance just change one cell in the array.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.

    Private Sub Workbook_Open()
    Application.CommandBars("Worksheet Menu Bar").Controls _
    ("Data").Controls("filter").Enabled = True
    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    will the user be looking for one set thing at a time.
    eg. "SMITH"
    or will they be looking for "SM......"
    or blanks?

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am filtering a query which is a large dbase, there are 8 different collums and many ways to search for data. I noteced the Advanced filter works with the code above, I didnt know if there was a way to get to the sub-menu Item. thx

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.
    advanced filter is still enabled with sheet protection but its requirements are different to autofilter and if your code doesn't take this into account (eg the need to enter a criteria range) maybe that's why it doesn' work. i have never used advanced filter and know nothing about vba so sorry can't advise anything.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    SOLUTION

    With Worksheets("myworksheet")
    .EnableSelection = xlUnlockedCells
    .Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
    .EnableAutoFilter = True
    .Protect contents:=True, userInterfaceOnly:=True
    End With

    It' taken me months to get this

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    put the above code in your sheets macro

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks Qroozn, I put it under my sheet1 general, but still only have the advanced filter option. Is it in the correct place ?

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is your autofilter saved on your worksheet?
    the macro should stop you being able to select anything except the autofilters already on the worksheet.

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
  •