protect check box and cells

[wynand]

I have the code below, which places the current date in the cell next to the
check box, if ticked and unticked. The next code I,ve tried but does not work
or gives a run time error.
Is there a way to after checking the box, protect both the cell and the
checkbox, and not any other cell?

date checkbox:

Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LCol As Long
Dim LRow As Long
Dim Rng As Range
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
'Find row that checkbox resides in
LCol = cBox.TopLeftCell.Column
LRow = cBox.TopLeftCell.Row
Set Rng = ActiveSheet.Cells(LRow + 0, LCol + 1)
'Change date in cell to the right of CheckBox, if checkbox is checked
If cBox.Value > 0 Then
Rng.Value = Date
Else
Rng.ClearContents
End If
End Sub

protection:

Private Sub worksheet_change(ByVal target As Range)

ActiveSheet. Unprotect
Selection.Offset(-1, 0).Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub

 

[Dave Peterson]

If you protect the checkbox, then you won't have a way to uncheck it. Is that
what you really want?

If yes, then drop the second procedure and try assigning this macro to each of
your checkboxes:

Option Explicit
Sub Process_CheckBox()

Dim cBox As CheckBox
Dim PWD As String

PWD = "hi"

Set cBox = ActiveSheet.CheckBoxes(Application.Caller)

ActiveSheet.Unprotect Password:=PWD
cBox.Enabled = False
With cBox.TopLeftCell.Offset(0, 1)
.NumberFormat = "mmmm dd, yyyy hh:mm:ss"
.Value = Now
.Locked = True
End With
ActiveSheet.Protect Password:=PWD

End Sub

 

[wynand]

Dave,
thanks a mil, works perfect

If you protect the checkbox, then you won't have a way to uncheck it. Is that
what you really want?

If yes, then drop the second procedure and try assigning this macro to each of
your checkboxes:

Option Explicit
Sub Process_CheckBox()

Dim cBox As CheckBox
Dim PWD As String

PWD = "hi"

Set cBox = ActiveSheet.CheckBoxes(Application.Caller)

ActiveSheet.Unprotect Password:=PWD
cBox.Enabled = False
With cBox.TopLeftCell.Offset(0, 1)
.NumberFormat = "mmmm dd, yyyy hh:mm:ss"
.Value = Now
.Locked = True
End With
ActiveSheet.Protect Password:=PWD

End Sub