CODEWITHSUNDEEP
vbaexcellockingconditional-formatting
Chris Williamson
Chris Williamson

Reputation: 35

Excel - How to conditionally lock and unlock a particular cell using VBA.

Title says it all. Any insight here? This code is most definitely not working:

Sub MrFreeze(ByVal Target As Range)

 Dim cCell As Range
 Dim wksInput As Worksheet

 Set wksInput = Worksheets("Input")
 Set cCell = wksInput.Range("D14")

 If cCell.Value = "Yes" Then
    ActiveSheet.Unprotect Password = "password"
    Else
    ActiveSheet.Protect Password = "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
 End If

End Sub

Thank you in advance for any help!

Upvotes: 0

Views: 7307

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If cell D14 contains Yes and the macro is run, the cell will be unlocked.
If cell D14 does not contains Yes and the macro is run, the cell will be locked.

Sub MrFreeze()

   Dim cCell As Range
   Dim wksInput As Worksheet

   Set wksInput = Worksheets("Input")
   Set cCell = wksInput.Range("D14")

   If cCell.Value = "Yes" Then
      ActiveSheet.Unprotect Password = "password"
         cCell.Locked = False
      ActiveSheet.Unprotect Password = "password"
   Else
      ActiveSheet.Unprotect Password = "password"
         cCell.Locked = True
      ActiveSheet.Protect Password = "password"
   End If

End Sub

Upvotes: 1

Related Questions