CODEWITHSUNDEEP
excelvba
Don Desrosiers
Don Desrosiers

Reputation: 143

data in one cell unlocks another

I want cell f21 to be locked if there is no entry (its a time entry if that is important) in f17. If a time is entered into f17, then f21 is subsequentially unlocked. Here's my code. It doesn't work at all.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Cells(6, 17) = blank Then
    ActiveSheet.Range(Cells(6, 21)).Locked = True
Else
    ActiveSheet.Range(Cells(6, 21)).Locked = False
End If
End Sub

What is wrong?

Upvotes: 0

Views: 24

Answers (2)

user6432984
user6432984

Reputation:

The worksheet needs to be protected for this to work. 

Private Sub Worksheet_Change(ByVal Target As Range)

    Cells(6, 21).Locked = IsEmpty(Trim(Cells(6, 17))

End Sub

If you set the UserInterfaceOnly to True when protecting a worksheet you will not have to unprotect the worksheet to programmable make changes.

ActiveSheet.Protect UserInterfaceOnly:=True

Upvotes: 1

Greig
Greig

Reputation: 15

Try it this way without the Range method

If ActiveSheet.Cells(6, 17) = blank Then
    ActiveSheet.Cells(6, 21).Locked = True
Else
    ActiveSheet.Cells(6, 21).Locked = False
End If

Upvotes: 1

Related Questions