• 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 to Protect Sheets locking Unlocked cells as well

Brandi

New Member
I have a macro that Protects all sheets in my workbook, but when used, it is locking cells that should be Unlocked.

Here is the Code:

>>> use code - tags <<<
Code:
Sub Protect_All()
    Dim xWs As Worksheet
    'turn screen updating off
    Application.ScreenUpdating = False
    'loop through each sheet and protect with a password
    For Each xWs In ActiveWorkbook.Worksheets
        xWs.Protect Password:="Password", _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
    Next xWs
    'turn screen updating on
    Application.ScreenUpdating = True
End Sub

Am I missing something?
 
Last edited by a moderator:
Brandi
Have You take care/marked someway those unlocked cells, before You've run above code?
Default would be lock... if no action, then all will be locked.
 
Brandi
Have You take care/marked someway those unlocked cells, before You've run above code?
Default would be lock... if no action, then all will be locked.
Yes, I have. Odd part is that it works on the first sheet, but none of the others, even though all sheets have only the cells with formulas locked. I just wasn't sure if there was a piece of code that I was missing?
 
Brandi
I tested You code ...
Should it work like this?
Yellow cells can write.

Do You have there more code?
 

Attachments

  • brandi.xlsb
    18.9 KB · Views: 2
I have attached a sample with all of my code.
The Sample seems to work. So, I am guessing it is something to do with the original file. It is a report that has been used for awhile that I was trying to add some helpful macros to, but not all are working.

However, in the Sample, when the sheets are protected the Blank_Cells_Filter will not work. It is the Only reason I had to create a macro to Protect and Unprotect. Dang It. Funny that the Unfilter works, but the Filter will not?
 

Attachments

  • SampleCR.xlsm
    71.4 KB · Views: 1
Brandi
You could test this version - and - You could delete those Protect & Unprotect -codes
>> use 'my' the last code instead of those!
... is it funny, if something won't work?
I could filter as well as unfilter all sheets ... hmm?
 

Attachments

  • SampleCR.xlsm
    72.7 KB · Views: 2
Brandi
You could test this version - and - You could delete those Protect & Unprotect -codes
>> use 'my' the last code instead of those!
... is it funny, if something won't work?
I could filter as well as unfilter all sheets ... hmm?
Thank You So Much!!!
That did it!!

I was in the beginning phase of rebuilding that Entire report! You just saved my weekend.

And, as for something being funny when it doesn't work, I Try to laugh rather than cry. I don't always succeed. LOL.
Thank You, Again!
 
@vletm
Next problem with this, I have pivot tables on one tab and power queries on two other tabs. I know there is a permissions for that, but when I added that to the Protect on Start, it did not allow me to update any of those tables. Would it be possible to not protect those three sheets? I tried adding the code for select sheets, but it didn't work.
I even took care to Unprotect the cells surrounding the pivots as there is only One cell that need protection on that tab.
 

Attachments

  • SampleCR.xlsm
    94.8 KB · Views: 3
Brandi
Ouch ... I replied above Your challenge to Your other thread :)
... and if no protect ... then You cannot eg delete those rows ... hmm?
... hmm? You didn't use that the newest DeleteRows with above file.
 
Back
Top