VBA Only Protect Populated Cells

[kaylan]

Hello,

I need to create a button that allows a manager to lock all data input, but will then allow other users to add to the sheet below what has already been locked. I know how to set up the button and password, but everything I do to protect the sheet ends up protecting everything -- including the empty cells. My understanding thus far is that the best solution is to code something that unlocks the empty cells.

The closest I have gotten is the code below (which I got from another site). The problem is that this formula is only unlocks empty cells if they are between populated rows. My sheet needs to have an unlimited number of rows. Simply adding something to the very last row of the sheet does work, but then the macro is slow to run -- not ideal. I know there has to be a better way to do this.

I plan on adding the code for the password once I get the locking/unlocking portion functional:

Sub UnlockEmptyCells()
Dim myCell As Range


ActiveSheet.Unprotect


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

 

[shknbk2]

Try it backwards: first unlocking all cells and then locking just the ones with values.

Sub UnlockEmptyCells()
    Dim myCell As Range
    
    Set myCell = Selection
    Cells.Select
    Selection.Locked = False
    myCell.Select
    Selection.SpecialCells(xlCellTypeConstants).Select
    Selection.Locked = True
    myCell.Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[kaylan]

Thank you for the suggestion!!

When I try the code suggested, I get Run-time error '1004': Unable to set the Locked Property of the Range class.

I'm going to play around with your code and see if I can get it to work, but if you have any other thoughts please let me know.

 

[kaylan]

Actually scratch that -- my first solution tried fixed it! I simply needed to unprotect the sheet first, then it works. Thank you so much for your help!

 

[shknbk2]

Sorry about that. My fault for not putting in the Unprotect line. Glad it worked in the end!