Link to home
Start Free TrialLog in
Avatar of swimehdi
swimehdiFlag for United States of America

asked on

Protect sheets upon exiting .

I am interested to know if there is a possibility for my sheets to become protected automatically upon exiting the workbook
Template3.xls
Avatar of swimehdi
swimehdi
Flag of United States of America image

ASKER

Thank you for the help
Avatar of zorvek (Kevin Jones)
Add this code to the ThisWorkbook code module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Sheet As Sheet1
    For Each Sheet In ThisWorkbook.Sheets
        Sheet.Protect "Password"
    Next Sheet

End Sub

Kevin
Thank you Kevin for the reply.
I am getting an error:

runtime error 13

Type mismatch.

File attached

Please help
Template4.xls
Fixed:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Sheet As Sheet
    For Each Sheet In ThisWorkbook.Sheets
        Sheet.Protect "Password"
    Next Sheet

End Sub

Kevin
I am getting a different error:  
"Compile Error:" "User-defined type not defined".  Thank you again
Template5.xls
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sweet!  It works like a charm

Thank you