Lock cell

[Ciccio86]

Hi, I'm trying to lock a range when I tick a checkbox.
Ex. If I tick the checkbox in C1 the range A1:B1 get locked
The sheet is already protected but I have editable ranges

But every time I tick the checkbox all the editable ranges get locked

Private Sub CheckBox1_Click()

    Dim chk As OLEObject

    Set chk = Me.OLEObjects(CheckBox1.Name)

    Call Lock(chk)

End Sub

Sub Lock(chk As OLEObject)
Dim r As Long
    r = chk.TopLeftCell.Row
    ActiveSheet.Unprotect Password:="Secret"
    Range("A" & r).Locked = True
    ActiveSheet.Protect Password:="Secret"
End Sub

 

[Jeffrey Mahoney]

I'm going to give you two solutions. The one you wanted with the checkboxes and my version with Wingding Characters in cells that get altered with a DoubleClick macro. I like using the Wingding checkboxes because I can copy them down to as many cells as I want. I named the range "CheckBoxes". The macro looks at that range to see if that is one of the cells you doubleclicked.

Book3
	A	B	C	D
1	Job Start	Job End	Status	Status2
2				þ
3				¨
4				¨
Sheet1

Private Sub CheckBox1_Click()
  Dim chk As OLEObject

    Set chk = Me.OLEObjects(CheckBox1.Name)
    
    Call ToggleLock(chk.TopLeftCell.Row, Me.CheckBox1.Value)
    
End Sub



Sub ToggleLock(ChkRow, ChkStatus As Boolean)
    ActiveSheet.Unprotect Password:="Secret"
    If ChkStatus = True Then
      Range("A" & ChkRow & ":B" & ChkRow).Locked = True
    Else
      Range("A" & ChkRow & ":B" & ChkRow).Locked = False
    End If
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, Password:="Secret"
End Sub



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Set i = Intersect(Target, Range("Checkboxes"))
  If Not i Is Nothing Then
    Cancel = True
    ActiveSheet.Unprotect Password:="Secret"
    Application.EnableEvents = False
    If i.Value = "þ" Then
      i.Value = "¨"
      ToggleLock i.Row, False
    Else
      i.Value = "þ"
      ToggleLock i.Row, True
    End If
    Application.EnableEvents = True
    
  End If
    
End Sub

 

[Jeffrey Mahoney]

You know how to create a named range right? The range with the Wingding characters needs to be unprotected.

 

[Ciccio86]

I'm going to give you two solutions. The one you wanted with the checkboxes and my version with Wingding Characters in cells that get altered with a DoubleClick macro. I like using the Wingding checkboxes because I can copy them down to as many cells as I want. I named the range "CheckBoxes". The macro looks at that range to see if that is one of the cells you doubleclicked.

Book3
	A	B	C	D
1	Job Start	Job End	Status	Status2
2				þ
3				¨
4				¨
Sheet1

View attachment 86456

Private Sub CheckBox1_Click()
  Dim chk As OLEObject

    Set chk = Me.OLEObjects(CheckBox1.Name)
 
    Call ToggleLock(chk.TopLeftCell.Row, Me.CheckBox1.Value)
 
End Sub



Sub ToggleLock(ChkRow, ChkStatus As Boolean)
    ActiveSheet.Unprotect Password:="Secret"
    If ChkStatus = True Then
      Range("A" & ChkRow & ":B" & ChkRow).Locked = True
    Else
      Range("A" & ChkRow & ":B" & ChkRow).Locked = False
    End If
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, Password:="Secret"
End Sub

Thanks For the fast reply.
With this code when i tick a checkbox all the sheet get locked and not only the checkbox row.

Edit: I have to update the status first time, is possible to update on workbook open?

 

[Jeffrey Mahoney]

You need to unlock the cells that the check boxes are in

It worked for me