VBA lock a portion of the worksheet based on cell value

**hinklejt** · 02-02-2014, 03:47 PM

Hey Everyone,

Im running into a little bit of a problem. I have a cell set up with an if statement that will return the value 1 or 0. What I am trying to accomplish is that once the value becomes 1 it will lock a portion of the worksheet. if the value were to change back to 0 it would then unlock the portion of the worksheet. Here is the code I am working with. I am no longer recieving any errors, but the code doesnt appear to be doing anything. Any suggestions would be greatly appreciated. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Select Case [v25]
    Case 1:
    If ActiveCell.Value = 1 Then
    Range("B7:N70") = Target
        Target.Locked = True
        Application.EnableEvents = True
               Me.Protect ("ppppp")
    
    Else
    Select Case [v25]
    Case 0:
    If ActiveCell.Value = 0 Then
        Target.Locked = False
        Me.Unprotect ("ppppp")
        Application.EnableEvents = False
                 
      
     End If
        
        
        End Select
    End If
    End Select
End Sub

**mrice** · 02-02-2014, 03:59 PM

You seem to be using the Target range for both a function level argument and then a different range.

You could try using a different name

e.g.

Dim Target2 as Range
.......

Set Target2 =  Range("B7:N70")

**Mumps1** · 02-02-2014, 04:05 PM

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("V25")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="ppppp"
    Select Case Target.Value
        Case Is = 1
            Range("B7:N70").Locked = True
        Case Is = 0
            Range("B7:N70").Locked = False
    End Select
    ActiveSheet.Protect Password:="ppppp"
End Sub