Protect All Sheets Then Save

Re: Protect All Sheets Then Close Workbook

Use the macro recorder to produce the code you want for protecting a sheet then insert it in this!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each Sheet In Sheets
'YOUR PROTECT CODE HERE
Next Sheet
ThisWorkbook.Save


End Sub

this code goes in the ThisWorkbook module.

Re: Protect All Sheets Then Save

Try this:

Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    With ws
        .Protect Password:="MyPassword", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, userinterfaceonly:=True
        .EnableAutoFilter = True
    End With
    Next ws

I tend to use it in the workbook open event, rather than close, but it will work either way.

Re: Protect All Sheets Then Save

Welcome to osgrid, velnoskt. Tell your friends about us.

Quote

Thanks, I tried this code and got a Compile Error: For Without Next What did I do incorrectly?

You left the Next statment out. Also you should Dim wsSheet as Worksheet (and always use Option Explicit)

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wsSheet As Worksheet


    For Each wsSheet In Sheets
        'YOUR PROTECT CODE HERE
        Sheet1.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                       True, AllowInsertingRows:=True, AllowFiltering:=True
        Sheet1.EnableSelection = xlUnlockedCells


        Sheet2.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                       True, AllowInsertingRows:=True, AllowFiltering:=True
        Sheet2.EnableSelection = xlUnlockedCells
        ThisWorkbook.Save
    Next wsSheet
End Sub

Re: Protect All Sheets Then Save

Thanks for picking that up Bill, if you were to use AAE's solution you would have to specify each and every sheet in the array, the way i supplied will run through each sheet regardless, and as Bill said if you use a variable for anything the Declare it otherwise Excel will just treat it as variant, here's the code tidied, you don't need to name each sheet like sheet1.protect.... sheet2.protect because you are using the same settings for each sheet!

Option Explicit 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Dim wsSheet As Worksheet
     
    For Each wsSheet In Sheets
        wsSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowInsertingRows:=True, AllowFiltering:=True
        wsSheet.EnableSelection = xlUnlockedCells
    Next wsSheet
    ThisWorkbook.Save
End Sub

Re: Protect All Sheets Then Save

Thanks AAE for you contribution

velnoskt, no need to put quote tags around your own posts. Your not quoting yourself

Re: Protect All Sheets Then Save

Velknost, you would have to have the code in another module like this:
these go in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call MyProtect
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call MyProtect
End Sub

This one goes in a standard module:

Sub MyProtect()
Dim wsSheet As Worksheet
    For Each wsSheet In Sheets
        wsSheet.Protect Password:="p", DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowInsertingRows:=True, AllowFiltering:=True
        wsSheet.EnableSelection = xlUnlockedCells
    Next wsSheet
     ThisWorkbook.Save
End Sub

Re: Protect All Sheets Then Save

Say Siman,

I don't understand your post?

Dim wsSheet As Worksheet 
    For Each wsSheet In Sheets 
        wsSheet.Protect Password:="p", DrawingObjects:=False, Contents:=True, Scenarios:= _ 
        True, AllowInsertingRows:=True, AllowFiltering:=True 
        wsSheet.EnableSelection = xlUnlockedCells 
    Next wsSheet 
    ThisWorkbook.Save

Works ok as standalone code in Thisworkbook BeforeClose or BeforeSave[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]velnoskt, Appreciate your inquire as to not break any rules.
All questions and answers should be posted to the forum. The goal of the forum is to teach and learn for all to see. How can the rest of us learn if you use Private Message or email?

Thanks for using ozgrid

Re: Protect All Sheets Then Save

Bill, i provided that alternative because of this at the end of Tom's post:

Quote from Tom

Simon & Bill, I had forgotten to see if just SAVING the document invoked the protection... it does not... Thoughts?
Thanks
Tom

I just thought it would better demonstrate to Tom the events that were happening rather than just copying the code to the Before_Save[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Also Bill Tom posted this:

Quote from Tom

I am assuming too that I can add other duties in this script before closing, such as unhiding all rows, and returning to A1 etc

so it saves on confusion for him if he is trying to integrate other code with what he already had in the before close, now he has a clean sheet to work with!

Re: Protect All Sheets Then Save

Thanks Simon

Re: Protect All Sheets Then Save

Well, i am glad that Bill, I and Ozgrid could help you out, we just push in the right direction we cant make you learn but if you get something from it all the better....all the best.