Lock only a few cells on a worksheet

Closed
ugn10 Posts 48 Registration date Saturday October 27, 2012 Status Member Last seen March 4, 2014 - Mar 16, 2013 at 06:32 PM
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 - Apr 3, 2013 at 04:30 AM
Hello,

I have an excel file having many formula and I want to lock all the cells having formula. Whatsoever cell address, it could be D1, E9, J10, etc.
Please suggest me that how to lock the specific cells in excel so that no one can make changes in the formula cells and the input cells.


I followed the following steps

http://office.microsoft.com/en-in/excel-help/lock-only-a-few-cells-on-a-worksheet-HA001054825.aspx

It's not working.

Pls suggest


Related:

2 responses

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 18, 2013 at 04:49 PM
Hi,

Steps mentioned in the link that you provided viz. "Locking" or "Hiding" specific cells, will not work unless the Worksheet is password protected. I use Microsoft Excel 2010, to password protect a worksheet we need to click on "Review" tab in the top ribbon and then select "Protect Sheet" and then choose a password.

Do reply with results.
0
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 3, 2013 at 04:30 AM
Hi,

I am not sure what r u going to achieve if you want to lock the cells which is mention in the d1,e9 etc

Please try the below code, if any problem please inform us

'
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
' declarations
' start
Me.Unprotect
Application.EnableEvents = False
' process
[A1:d20].Locked = True
If [A1].Value = "Un Lock" Then
[A5:A20].Locked = False
End If
If [B1].Value = "Un Lock" Then
[B5:B20].Locked = False
End If
If [C1].Value = "Un Lock" Then
[C5:C20].Locked = False
End If
If [D1].Value = "Un Lock" Then
[D5:D20].Locked = False
End If

' end
Application.EnableEvents = True
Me.Protect
End Sub
'

Thanks


Patnaik
0