Sub UnlockEmptyCells()
Dim myCell As Range
Set myCell = Selection
Cells.Select
Selection.Locked = True
myCell.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
myCell.Select
End Sub
Comments:
- Running this macro will leave all cells inside the input area unlocked if they are empty. All cells outside the input area will be locked including the empty ones. For Example, if the most bottom right cell with input is G20 all the columns from H and the rows from 21 will be locked.
- If you need a wider range just add spaces to the most bottom right cell first thing in the macro to let it be the endpoint cell.
- The macro won't run twice in a row. You need to unprotect the sheet before you run it next time. You can add the appropriate 'unprotect' code to the macro to avoid manual coding or record such a mcro and use two buttons, one to protect and the other to unprotect.
- This tip applies to 2003, 2007 and 2010.
Thanks for the blog post buddy! Keep them coming... Microsoft Excel Training Courses Malaysia
ReplyDelete