Forum Discussion

Copper Contributor

Apr 19, 2022

Solved

Locking down a date with conditional formatting

Hello, I am not very familiar with conditional formatting but the situation is as follows: In column A I have gotten dates in Colomn B I have the expire dates and in Column C i have Check b...

developer

excel

Formulas and Functions

Apr 20, 2022

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

HansVogelaar

MVP

Apr 19, 2022

Jliersenvlst

Did you use Form controls or ActiveX controls for the check boxes?

Jliersenvlst
Copper Contributor
Apr 19, 2022
I used Form Controls
- HansVogelaar
  MVP
  Apr 19, 2022
  Jliersenvlst
  
  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 Sub
  
  and 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.
  CheckBoxes.xlsm19 KB
  - Jliersenvlst
    Copper Contributor
    Apr 20, 2022
    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 🙂