How to lock previously unlocked cells on a protected worksheet

**susuyesyes** · 12-06-2012, 02:31 PM

Please help. A password protected monthly worksheet contains unlocked cells for users enter value. How to make all cells on the worksheet locked automatically and not allow any data input in next month? There is a date cell on each worksheet, how to make every worksheet protected in next month?

Thank you very much for your help.

**aelgadi** · 12-06-2012, 03:42 PM

Do you want to use one macro to lock all sheets at once?

Here an example for locking and protecting all sheets at once. You can modify it to make it work for specific sheet (without loop)

Sub LockAllSheets()
    For Each Sh In ThisWorkbook.Sheets
        bVisible = Sh.Visible
        If Sh.Visible <> xlSheetVisible Then Sh.Visible = xlSheetVisible
        Sh.Unprotect "Pwd"
        Sh.Cells.Locked = True
        Sh.Protect "Pwd"
        Sh.Visible = bVisible
    Next Sh
End Sub

**susuyesyes** · 12-06-2012, 03:54 PM

Originally Posted by aelgadi

Do you want to use one macro to lock all sheets at one?

No. I want each sheet that not belongs to the current month automatically locked itself in the current month. That way the current month worksheets are still available for data input.

Thank you very much for your help!!!!

**aelgadi** · 12-06-2012, 04:40 PM

But you need to triger the macro that lock past month somehow let's say for example when you insert a new sheet or when save the document what you think?

**susuyesyes** · 12-06-2012, 04:46 PM

Thank you for helping me. The workbook is protected so no new sheet is allowed. I would prefer all past months sheets locked when user opens the workbook. Thank you.

**aelgadi** · 12-06-2012, 04:52 PM

>>> If satisfied with a solution, don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

>>> Thanks who helped by clicking the star below the post.