asked on

Excel worksheet update two different cells

I had this question after viewing Excel 2010 disable a cell based on another cell.

I am using this to so that if one cell is populated then another is not updatable. Is it possible to have another change option in a worksheet?

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
'The code will be triggered if the cell being changed is in column D, starting from Row10
If Target.Column = 4 And Target.Row > 9 Then

ActiveSheet.Unprotect Password:=PW
If Target <> "" Then
Cells(Target.Row, "E").Locked = True
Else
Cells(Target.Row, "E").Locked = False
End If
ActiveSheet.Protect Password:=PW
End If
End Sub

Pdeters

ASKER

Can you have more than one Target

Private Sub Worksheet_Change(ByVal Target As Range)

Pdeters

ASKER

I guess the question is can I have more than one private Sub

Shums Faruk

Which other columns should be disabled?
You can have same Private Sub for another range.

Pdeters

ASKER

What I want to do is another column has a value then disable another column. columns are not related

Shums Faruk

BTW, I don't see your previous accepted solution works, I can enter any where in Col E even Col D has value or blank.
LockCells.xlsm

Martin Liss

If you are asking if there can be more code in the change event then yes, definitely.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
'The code will be triggered if the cell being changed is in column D, starting from Row10
If Target.Column = 4 And Target.Row > 9 Then

    ActiveSheet.Unprotect Password:=PW
    If Target <> "" Then
        Cells(Target.Row, "E").Locked = True
    Else
        Cells(Target.Row, "E").Locked = False
    End If
    ActiveSheet.Protect Password:=PW
End If

If Target.Row = 27 Then
    MsgBox "This is a test"
End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION