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:
- Selects each sheet in the file.
- Checks whether its name is different from “SomeSheetName“
- Activates the sheet
- Locks range C1 and A1:A40.
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub <span class="hiddenSpellError">UnlockAll</span>() Dim sheet As Worksheet For Each sheet In ThisWorkbook.Worksheets sheet.Activate ActiveSheet.Unprotect Password:="<span class="hiddenSpellError">vitoshacademy</span>" ActiveSheet.Cells.Locked = False Next sheet End Sub |
And here is the code for the first example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub <span class="hiddenSpellError">LockSomething</span>() Dim sheet As Worksheet For Each sheet In ThisWorkbook.Worksheets If (sheet.Name <> "<span class="hiddenSpellError">SomeSheetName</span>") And (sheet.Name <> "<span class="hiddenSpellError">OtherSheetName</span>") Then Debug.Print sheet.Name sheet.Activate Range("<span class="hiddenSpellError">C1</span>").Locked = True Range("A1:<span class="hiddenSpellError">A40</span>").Locked = True ActiveSheet.Protect Password:="<span class="hiddenSpellError">vitoshacademy</span>" End If Next sheet End Sub |
🙂