Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: PROTECT A SHEET AND ENABLE AUTO FILTER - Code provided

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

    Default

    BELOW IS CODE TO ENABLE AUTOFILTER ON A worksheet when we dont want the user to adjust the sheet in any other way. I know people have always had trouble with it


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

    [ This Message was edited by: Qroozn on 2002-04-17 20:18 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Qroozin - VERY useful piece of code - thanks.

    Having alittle trouble getting it to work.
    I've entered "sheet1" in place of "myworkbook", and I'm getting error 1004.
    it doesn't like something in the .Protect method.
    Any ideas?

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

    Default

    Hi Blue, Q posted that code yesterday, works great. This is what I have in my workbook, hope it helps.

    Private Sub Workbook_Open()
    With Worksheets("sheet1")
    .EnableSelection = xlUnlockedCells
    .Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
    .EnableAutoFilter = True
    .Protect contents:=True, userInterfaceOnly:=True
    End With
    End Sub


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok guys, what am I missing?
    at first I thought the code would allow someone to go to a protected sheet, and set and use autofilters.
    I now believe the code is designed to allow the developer to set the autofilters, THEN protect the worksheet, and have the autofilters active.

    regardless-
    I can't get it to work..
    I've tried putting code into worksheet via VBE, and I've tried putting code into a button... no joy in either case.

    please advise?

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
  •