Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect check box and cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect check box and cells
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect check box and cells
Dave,
thanks a mil, works perfect "Dave Peterson" wrote: 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 wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect worksheet with check box | Excel Discussion (Misc queries) | |||
protect worksheet with check box | Excel Discussion (Misc queries) | |||
Macro to allow spell check with protect sheet | Excel Programming | |||
How can I protect a worksheet but still use ActiveX check-boxes? | Excel Discussion (Misc queries) | |||
Lock and protect cells without protect the sheet | Excel Programming |