Protect Non Blank Cells

[niljoy]

Hi,


Need macro to protect non blank cells of entire worksheet.


Request you to please help me with VBA Script.


Thanks.

 

[SirJB7]

Hi, niljoy!


Try this:

-----

[pre]

Option Explicit

Sub AndHowYouUnprotectThem()
With ActiveSheet
.Cells.Locked = True
.Cells.SpecialCells(xlCellTypeBlanks).Locked = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

[/pre]
-----


Regards!

 

[niljoy]

Hello,


Sorry it is not working.... Blank cells are also getting protected....


Request you to please help in this regards....

 

[mgesh2002]

Should you need to protect only through VBA?


If not, then, why dont you try these 3 steps?


1. Select whole worksheet ('Ctrl + A' from blank cell). Go to Format cells (Ctrl + 1) then Protection tab. Uncheck Locked option press OK.


2. Then, select the editable ranges (non blank cells) and go to Format cells (Ctrl + 1) then Protection tab. Check Locked option press OK.


3. Now, go to review tab, click on protect sheet, let the two options selected, use password if needed (but, dont forgot the password) then press OK and DONE.

 

[SirJB7]

Hi, niljoy!

That was the short and quick one step method and yes, it leaves unprotected blank cells only within the used range. If you need to have all the worksheet blank cells you'd have to use two steps method:

-----

[pre]

Option Explicit

Sub AndHowYouUnprotectThem()
With ActiveSheet
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeConstants).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

[/pre]
-----

Regards!