1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protect sheet with VBA Code with command button BUT allow autofilters

Discussion in 'VBA Macros' started by Juzar22, Apr 1, 2017.

  1. Juzar22

    Juzar22 New Member

    Messages:
    28
    Code (vb):
    Private Sub CommandButton1_Click()
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    If wSheet.Name = "Planet" Then
    wSheet.Unprotect Password:=Text123
    ElseIf wSheet.ProtectContents = True Then
    wSheet.UnProtect Password:=Text123
    Else
    wSheet.Protect Password:=Text123
    End If
    Next wSheet
    Above code helps to Protect/Unprotect worksheet but it does not allow autofilter to run, how can i fix this ?
  2. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Have You tested something like this:
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
  3. Juzar22

    Juzar22 New Member

    Messages:
    28
    Can you please tell me where should i insert this code i mean after which line ?
  4. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Like this ...
    wSheet.Protect Password:=Text123, AllowFiltering:=True
  5. Juzar22

    Juzar22 New Member

    Messages:
    28
    I tried it gives me Compile error: Named argument not found


    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

  6. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Test like this:
    wSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
    or what would be Your Text123 (variable)
  7. Juzar22

    Juzar22 New Member

    Messages:
    28
    I added the line as you mentioned but Same error again
  8. Monty

    Monty Well-Known Member

    Messages:
    762
    Hello Juza.

    Try this.

    Code (vb):
    With Worksheets("Sheet1")
    .Protect Password:="pass", userinterfaceonly:=True
    .EnableAutoFilter = True
    End With
    Juzar22 likes this.
  9. Juzar22

    Juzar22 New Member

    Messages:
    28
    Appreciate but this code only protects with the click with same click unprotect also required
  10. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Juzar22
    You wrote something about ADD ...
    Not add, change as I wrote!
  11. Juzar22

    Juzar22 New Member

    Messages:
    28
    Sorry but i am new in VBA where should i add this after which line
  12. vletm

    vletm Well-Known Member

    Messages:
    2,598
    ADD means ADD
    CHANGE means CHANGE
    those no matter VBA or not!
    Code (vb):

    PrivateSub CommandButton1_Click()
    Dim wSheet As Worksheet
    ForEach wSheet In Worksheets
    If wSheet.Name = "Planet"Then
      wSheet.Unprotect 'Password:= Text123
    ElseIf wSheet.ProtectContents = TrueThen
      wSheet.UnProtect 'Password:= Text123
    Else
      wSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
    EndIf
    Next wSheet
    End Sub
     

    I also edit those Password:= Text123 AWAY
    ... or do someone know what is value of Text123?
  13. Juzar22

    Juzar22 New Member

    Messages:
    28
    Same error - Compile error: Named argument not found
  14. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Upload that file ...
    or
    at least copy Your code!
  15. Juzar22

    Juzar22 New Member

    Messages:
    28
    File Uploaded

    Attached Files:

  16. vletm

    vletm Well-Known Member

    Messages:
    2,598
    ... hmm ...
    You didn't make as I wrote with #12 Reply.
    Test this!

    Attached Files:

    Juzar22 likes this.
  17. Juzar22

    Juzar22 New Member

    Messages:
    28
    Same error of Named argument not found

    upload_2017-4-2_12-39-13.png
  18. vletm

    vletm Well-Known Member

    Messages:
    2,598
    For me it works without Errors.
    ... try to save file to .xlsx -format.
    ... or which Excel-version You use?
    Juzar22 likes this.
  19. Juzar22

    Juzar22 New Member

    Messages:
    28
    Can you see attached file i have used previous code which you give me in serial no 8 which work perfect i can use autofilter when the file is locked but only thing missing is again with same button Unlock file - check attached file

    Attached Files:

    Last edited: Apr 2, 2017
  20. vletm

    vletm Well-Known Member

    Messages:
    2,598
    You try to 'Google' Excel 2000 case of protect
    or
    maybe someone else would remember.
    Juzar22 likes this.
  21. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    Hi vletm !

    He just must read inner VBA help, all is there, that's it !
  22. vletm

    vletm Well-Known Member

    Messages:
    2,598
    Juzar22
    Test next:
    1) open that code for view
    2) select 'Protect'-word from code
    3) (... You use Windows) press <F1> and find which parameters can use with Your version
    4a) or with mouse's right click - select 'Object Browser'
    4b) write to top rows second box protect and soon You could find more information below - - good luck!
    5) use possible parameter and be happy/lucky
    Juzar22 and Marc L like this.
  23. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    Hi !

    Juzar22, see this video from my local Excel 2003 Windows version

    where I first put text cursor on statement before to hit [​IMG] key …
    Juzar22 and vletm like this.
  24. Juzar22

    Juzar22 New Member

    Messages:
    28
    Really appreciate your time and effort , if found the solution after your video presentation and also took help from previous replies by ''vletm''

    **Thank You**
    **Thank You**
  25. Juzar22

    Juzar22 New Member

    Messages:
    28

    Thank You for your effort for finding out solution for me specially the problem of old excel version, really appreciate it..

Share This Page