Excel-VBA Before Save Lock a range of cells

Question

I am generating a Daily Planner Sheet in which i want to lock some appraisal cells after saving. I have written the following code in excel workbook code. The macro asks to enter password before saving. Why is it asking to enter the password?(I have 53 sheets for weekly planning. I have shown only 2 here)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet18").Unprotect Password:="****"
Sheets("Sheet19").Unprotect Password:="****"
ActiveSheet.Protect Contents:=False
For Each Cell In Range("H5:H24,J5:J24")
If Cell <> "" Then Cell.Locked = True
If Cell = "" Then Cell.Locked = False
Next
ActiveSheet.Protect Contents:=True
Sheets("Sheet18").Protect Password:="****"
Sheets("Sheet18").Protect UserInterfaceOnly:=True
Sheets("Sheet19").Protect Password:="****"
Sheets("Sheet19").Protect UserInterfaceOnly:=True
End Sub

d-stroyer · Answer 1 · 2013-07-11T11:39:27.687

Your code will behave differently depending on the active sheet when saving.

Additionnaly, I wouldn't recommend to overload the reserved name "Cell" with a local loop variable. This will lead to unexpected behavior.

You should remove references to ActiveSheet. If your wish is to protect the entire workbook, I would suggest iteration over the worksheets:

Sub ProtectAll()
    Dim wSheet As Worksheet
    Dim myCell As Range
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:="****"
        For Each myCell In Range("H5:H24,J5:J24")
            myCell.Locked = (myCell <> "")
        Next myCell
        wSheet.Protect Contents:=True, Password:="****", UserInterfaceOnly:=True
    Next wSheet
End Sub

NB: you have to put the code in a code module.

I just updated the code sample to show how to use it. If you have a runtime error, please tell which error code and at which line ... helping goes both ways. — d-stroyer, Jul 11 '13 at 11:40

Excel-VBA Before Save Lock a range of cells

1 Answers1