• 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...

  • 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

Juzar22

Member
Code:
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 ?
 
Have You tested something like this:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
 
I tried it gives me Compile error: Named argument not found


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
 
Test like this:
wSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
or what would be Your Text123 (variable)
 
Hello Juza.

Try this.

Code:
With Worksheets("Sheet1")
.Protect Password:="pass", userinterfaceonly:=True
.EnableAutoFilter = True
End With
 
ADD means ADD
CHANGE means CHANGE
those no matter VBA or not!
Code:
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?
 
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
 

Attachments

  • Planet Another code.xls
    39 KB · Views: 3
Last edited:
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
 
Hi !

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

where I first put text cursor on statement before to hit
f1.gif
key …
 
Hi !

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

where I first put text cursor on statement before to hit
f1.gif
key …

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**
 
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


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