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

VBA Protect All Worksheets Keep Individual Sheet Protection Criteria

J

JenniferS

Member
Hello,

I would like to protect all worksheets but some sheets have different criteria for protection. For instance some allow pivot table and pivot chart, sort, etc. Not all the same. Is there a macro that can handle this?

Thank you very much
 
Probably not one that already exists, but you can certainly write one, protecting each worksheet using whatever kind of protection you want for each sheet.

You'd want the program to cycle through all the worksheets in the workbook:
Code: 
  For each ows in ThisWorkbook.Worksheets
    ' Here you look at ows.Name and apply the correct protection.
    Next ows
If you don't know how protection is done in VBA, record a macro and protect a sheet; then go look at the code Excel recorded, and use that as your model.
 
What is wrong with my codes? I get errors on the drawing objects and autofilters.

>>> use code - tags <<<
Code: 
Sub ProtectWorkbook()
    Dim wsheet As Worksheet
    For Each wsheet In ActiveWorkbook.Worksheets
        wsheet.Protect , DrawingObjects:=False, AutoFilter:=False
       
    Next wsheet
    Range("F3").Select
    ActiveWorkbook.Save
End Sub

Sub UnProtectWorkbook()
    Dim wsheet As Worksheet
    For Each wsheet In ActiveWorkbook.Worksheets
        wsheet.UnProtect , DrawingObjects:=True, AutoFilter:=True
       
    Next wsheet
End Sub
 
LOL, whenever you say you "got an error", it's generally a good idea to quote the error message. A type mismatch is not the same as "this object does not support this property or method", and so on. What error did you get?

Off-hand I don't see anything wrong with DrawingObjects. But when I look at the documentation for the Protect method, it doesn't mention an optional argument named AutoFilter.
 
I have no idea what I am doing. I just try different ways to get the protection on the sheets that work the same as declaring in the ribbon.

I need to allow select unlocked cells, allow autofilter, and allow drawing objects.

These need to be in the VBA please.

Thank you
 
JenniferS
Could You check next link?
docs.microsoft.com

Worksheet.Protect method (Excel)

Office VBA reference topic
docs.microsoft.com
Syntax
expression.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
 
I tried this and it worked:

Code: 
Sub ProtectWorkbook()
    Dim wsheet As Worksheet
    For Each wsheet In ActiveWorkbook.Worksheets
        wsheet.Protect DrawingObjects, AllowFiltering
    Next wsheet
    Range("F3").Select
    ActiveWorkbook.Save
End Sub
 
I'm a little surprised. I would have thought, from reading the documentation, that the Protect method would simply take those two optional arguments, DrawingObjects and AllowFiltering, as the names of two variables that it would plug in for the first two arguments in the call to the Protect method, ie Password and DrawingObjects. If I had been right, it would be just like saying
Code: 
wsheet.Protect "", False
....setting the password to nothing and DrawingObjects:=False. AllowFiltering, in that case, would take the default value of False.

Clearly, if this works for you, I must have been wrong.
 
Well I was wrong. The protection isn't doing what I expected.

I want to be able to only select Unlocked cells on all sheets and also on the Invoice Payment sheet allow the slicers to function.

Can this be done please?
 

Attachments

  • Invoice Inventory 100.1.xlsb
    197.3 KB · Views: 4
My original suggestion was that you turn on macro recording and manually protect a sheet the way you want it done. Then you can turn off macro recording and go look at the code that Excel wrote down, and see how it works. Did you ever try that?

Frankly, I don't use protection that much, so I don't feel qualified to just tell you how to do it. But macro recording oughta give you a start, and when you compare that to the VBA documentation maybe it'll make sense. And you can ask more questions here, once you're ready to ask more specific questions.
 
This was the code that does work after recording how Excel does the macro:

Code: 
Sub ProtectWorkbook()
    Dim wsheet As Worksheet
    For Each wsheet In ActiveWorkbook.Worksheets
    
    wsheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFiltering:=True
        
    Next wsheet
   Worksheets("Menu").Activate
    
    
    Range("F3").Select
    ActiveWorkbook.Save
End Sub
 
Dunno whether you care, at this point now that you have something that works, but in the future when you use macro recording, keep in mind: It's a handy thing (a very handy thing), but Excel often writes out a lot of arguments that you don't need to worry about. In the long run it'll pay you, in my opinion, to take the recorded statements to the documentation and figure out what parts you can cut out, so that your program is simpler.

But maybe that's only if you have ambitions of learning to write more programs :).

Speaking of the documentation, I spent a few years looking for the good stuff before I finally found it and quit floundering around. My favorite URL for VBA programming is https://docs.microsoft.com/en-us/office/vba/api/overview/. From there you can select one of the Office apps (Excel in this case), or another one on the VBA language itself.
 
You must log in or register to reply here.
Back
Top