Previous Topic Next Topic

Lock only a few cells on a worksheet

[Closed]
Report
Posts
48
Registration date
Saturday October 27, 2012
Status
Member
Last seen
March 4, 2014
 -
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
 -
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


2 replies

Answer 1 / 2
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 46
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.
Answer 2 / 2
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
 44
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
Ask a question