Lock only a few cells on a worksheetClosed

Question

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


Configuration: Windows 7 / Chrome 25.0.1364.172

Zohaib R · Answer

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.

sgmpatnaik · Answer

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

Lock only a few cells on a worksheet

2 replies

Subscribe To Our Newsletter!