CODEWITHSUNDEEP
vbaexcel
venkat ch
venkat ch

Reputation: 63

Dynamically locking cells in Excel

I have four columns (A,B,C,D). My requirement is that in each row, only one cell should accept data, and the remaining cells should not accept data (i.e. the remaining three cells should be in locked mode).

How do I do this?

Upvotes: 0

Views: 7618

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

Here's one way to do what you ask. (Or at least what I think you're asking.)

First, unlock all the cells in the sheet. By default they are all in locked state (though this does not take effect until you actually protect the sheet) so you need to unlock them to start with, otherwise you won't be able to input any data anywhere as soon as you protect the sheet. You can do this manually (In 2003: Select all cells, Format > Cells > Protection > uncheck "Locked") or using VBA as follows:

Sub UnlockAll()
    With Sheet1 ' Or whatever sheet you're working on
        .Unprotect
        .Cells.Locked = False
        .Protect
    End With
End Sub

The above only needs to be executed once.

Then you have to use the Worksheet_Change event to lock and unlock cells dynamically. In your sheet's module, write this code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Unprotect
    If Not IsEmpty(Target) Then
        'Data was added in target cell. Lock its neighbours.
        Me.Cells(Target.Row, 1).Resize(, 4).Locked = True
        Target.Locked = False
    Else
        'Data was erased from target cell. Release its neighbours.
        Me.Cells(Target.Row, 1).Resize(, 4).Locked = False
    End If
    Me.Protect
End Sub

Say you write data in cell B2; this will lock cells B1, B3, and B4 so that no data can be entered. If you later decide to clear cell B2, then this will unlock B1, B3, and B4.

Upvotes: 7

Related Questions