VBA – Locking and unlocking cells in a range – quickly

The case – you have a multi-sheet Excel file, where you need to lock each cell in a given sheet. You have an option to do it manually, but it takes time and you need twice the time to check later. Or you may use VBA and be happy about it.

The choice is yours 🙂

Here is what the VBA does:

1. Selects each sheet in the file.
2. Checks whether its name is different from “SomeSheetName“
3. Activates the sheet
4. Locks range C1 and A1:A40.
5. Sets a password “vitoshacademy” for it.

Once this is done, you are a happy owner of  a well locked sheet. If you want to unlock it, again you have two options. Option A – to do it manually and then to lose time for checking it sheet by sheet and option B – to use 8 lines of VBA code. It’s your choice, but if you want to use the VBA, here comes the code:

Sub UnlockAll()
    Dim sheet As Worksheet

    For Each sheet In ThisWorkbook.Worksheets
        sheet.Activate
        ActiveSheet.Unprotect Password:="vitoshacademy"
        ActiveSheet.Cells.Locked = False

    Next sheet

End Sub

And here is the code for the first example:

Sub LockSomething()
    Dim sheet As Worksheet

    For Each sheet In ThisWorkbook.Worksheets
        If (sheet.Name <> "SomeSheetName") And (sheet.Name <> "OtherSheetName") Then
            Debug.Print sheet.Name
            sheet.Activate
            Range("C1").Locked = True
            Range("A1:A40").Locked = True
            ActiveSheet.Protect Password:="vitoshacademy"
        End If
    Next sheet
End Sub

🙂