Forum Discussion
Locking down a date with conditional formatting
Thanks. Since the formula in column O refers to column J instead of to the column immediately to the left, we have to change the formula:
Sub CheckBox_Click() Dim nme As String Dim shp As Shape Dim rng As Range nme = Application.Caller Set shp = ActiveSheet.Shapes(nme) Set rng = shp.TopLeftCell If shp.ControlFormat.Value = 1 Then rng.Offset(0, -1).Value = Date Else rng.Offset(0, -1).FormulaR1C1 = "=RC10+10" End If End Sub
You can create a macro like this:
Sub CheckBox_Click()
Dim nme As String
Dim shp As Shape
Dim rng As Range
nme = Application.Caller
Set shp = ActiveSheet.Shapes(nme)
Set rng = shp.TopLeftCell
If shp.ControlFormat.Value = 1 Then
rng.Offset(0, -1).Value = Date
Else
rng.Offset(0, -1).FormulaR1C1 = "=RC[-1]+10"
End If
End Suband assign it to all of the check boxes.
See the attached sample workbook. It is a macro-enabled workbook, so you'll have to allow macros when you open it.
HansVogelaar That almost did it for me, it is something I was looking for thanks. But I have gotten a question
Now I have checked it see image below
Checked
But when I unchecked it, it returns to the 1900's not the previous date, is there a way to fix that?
Unchecked and it returns to the 1900's
Nevertheless thank you so much 🙂
In your first post, you wrote that the date in column B is 10 days after the date in the column to the left.
When you clear the check box, the code restores the formula that adds 10 days to the date in the cell to the left.
But in your real setup, the start date is probably not in the cell to the left.
So I'd need to know which column to look at.
This is the setup. The one with the macro is in Column P and the formula in Column O is simple: =J4+10 within a conditinal rule.
Hope this helps.