Locking(Protecting) Unused Cells

[madhuchelliah]

Hey guys, how to lock(protect) unused cells in a sheet using macro.Sheet data will be dynamic.Thank you.

 

[Joe4]

What is the layout of your sheet?
Do you a "block" of data, where your want everything to the right of it, and everything underneath it locked?
Is it that straightforward?
Or might you have mixed blanks cells interspersed with your data that you also want locked?

 

[madhuchelliah]

My data always will be in C to I column but the rows are dynamic. I don't want others to add additional data in the sheet. So i want to lock the unused cells. If its fixed range of data then it will be easy to lock. But here the data is dynamic so i have no clue how to do it. I do have blank cells in between, but that doesn't matter. I want to lock after the last row used and the cells around C and I column.

 

[Joe4]

So, tell me if this sounds right.
- We can dynamically find the last row of data by looking in column C
- Once we find that, we want to leave columns C-I from rows 1 down to the last row unlocked, and lock EVERYTHING else.

Is that correct?
If so, this code should do what you want:

Sub MyProtectMacro()

    Dim lRow As Long
    
'   Use column C to find last row
    lRow = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Unprotect columns C-I down to last row
    Range("C1:I" & lRow).Locked = False
    
'   Protect sheet with password
    ActiveSheet.Protect Password:="pass"

End Sub

 

[madhuchelliah]

Thanks Joe, it working great. but one of the sheet is not locking any unused cells i don't know what is the reason. Do you have any idea on this?

 

[Joe4]

On any new blank sheet, all cells are "locked" to begin, by default. But if someone has already unlocked them all, you will need to lock them all to start.
We can add that to the code, like this:

Sub MyProtectMacro()

    Dim lRow As Long
    
[COLOR=#ff0000]'   Make sure all cells are locked to start
    Cells.Locked = True[/COLOR]
    
'   Use column C to find last row
    lRow = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Unprotect columns C-I down to last row
    Range("C1:I" & lRow).Locked = False
    
'   Protect sheet with password
    ActiveSheet.Protect Password:="pass"

End Sub

 

[madhuchelliah]

All the cells are locked only. The cells after I columns are locked. The cells below the last used row and column A and B are not locked.

 

[Joe4]

All the cells are locked only. The cells after I columns are locked. The cells below the last used row and column A and B are not locked.

I don't see how that is possible.
The code begins of locking ALL cells in on the sheet.
Then it only unlocks certains rows in column C-I. It doesn't touching columns A or B for the unlocking.

You don't have any merged cells, do you?
Or any other VBA code running?

 

[madhuchelliah]

Hi Joe, now its working after closing and open the WB again. i don't know how it got fixed. Thanks for your efforts....