UnCheck checkboxes after 1, 7, 30, 90 and 365 days

[marcusmellgren]

Hi,

I am really new to Excel and I am really over my skill set. But I really want to have a tasklist, with daily, weekly, monthly, quarterly and yearly tasks.
I also have a checkbox on each row on the different sheets, since I have the dailies on the daily sheet and so on.

Is it possible to program the checkbox to auto UNcheck after x days from I clicked the checkbox as TRUE?

I have the file here: tasklist1.xlsx - Delafil.se if someone wants to have a look.
No virus what-so-ever on that file.

Since Im quite bad at Excel, I find it hard to explain. But in the picture you see what I mean, this is a yearly task, and when I click checkbox in column B I want it to turn into FALSE after 365 days.
What do you think?

tasklist1.xlsx
	A	B	C	D	E
1	TRUE-FALSE	CHECKBOX	REF	TYPE	ACTIVITY
2	FALSE				Exempel: Lämna in deklarationen
3	FALSE
4	FALSE
5	FALSE
6	FALSE
7	FALSE
8	FALSE
9	FALSE
10	FALSE
11	FALSE
12	FALSE
YEARLY

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:N46	Expression	=$A2=TRUE	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:D12	List	=LISTS!$B$3:$B$10

Marcus

 

[John_w]

Welcome to MrExcel forums.

You can do this with VBA code, which means saving the workbook as a macro-enabled workbook (.xlsm file).

Put this code in a standard module:

Option Explicit

Public Sub Set_OnAction_CheckBoxes_All_Sheets()
  
    Dim ws As Worksheet
    Dim cb As CheckBox
  
    'Set the OnAction property of all checkboxes on all sheets to "CheckBox_Click"
  
    For Each ws In ThisWorkbook.Worksheets
        For Each cb In ws.CheckBoxes
            cb.OnAction = "CheckBox_Click"
        Next
    Next
  
End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
  
    'This macro is called when any Checkbox is clicked.  Application.Caller is name of Checkbox that was clicked
  
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        If cb.Value = xlOn Then
            'The checkbox is Ticked - store current date in column O cell
            .Cells(cb.TopLeftCell.Row, "O").Value = Date
        Else
            'The checkbox is Unticked - clear column O cell
            .Cells(cb.TopLeftCell.Row, "O").ClearContents
        End If
    End With
  
End Sub

Put this code in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim ws As Worksheet
    Dim daysOld As Long
    Dim r As Long
  
    Set ws = Sh
    With ws
        'Set the number of days old depending on the current sheet name
        Select Case .Name
            Case "DAILY": daysOld = 1
            Case "WEEKLY": daysOld = 7
            Case "MONTHLY": daysOld = 30
            Case "QUARTERLY": daysOld = 90
            Case "YEARLY": daysOld = 365
        End Select
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If IsDate(.Cells(r, "O").Value) Then
                'A date is in column O cell, is it older than the specified number of days?
                If Date - .Cells(r, "O").Value >= daysOld Then
                    'Yes, so untick the checkbox in the same row by changing its linked cell in column A to False and clear the date in column O
                    .Cells(r, "A").Value = False
                    .Cells(r, "O").ClearContents
                End If
            End If
        Next
    End With
  
End Sub

Next, run the Set_OnAction_CheckBoxes_All_Sheets macro. You only need to run this macro once, or after you add new checkboxes. This macro assigns the CheckBox_Click macro to every checkbox on every sheet.

The idea is that when you click a checkbox the CheckBox_Click macro is run and, if the checkbox is ticked, it stores the date it was ticked in the column O cell, otherwise it clears the cell. And when you activate a sheet, the Workbook_SheetActivate event handler is run which looks at all checkboxes on the sheet and clears those which have a 'tick date' older than the specified number of days. More details are in the code comments.

 

[marcusmellgren]

Welcome to MrExcel forums.

You can do this with VBA code, which means saving the workbook as a macro-enabled workbook (.xlsm file).

Put this code in a standard module:

Option Explicit

Public Sub Set_OnAction_CheckBoxes_All_Sheets()
 
    Dim ws As Worksheet
    Dim cb As CheckBox
 
    'Set the OnAction property of all checkboxes on all sheets to "CheckBox_Click"
 
    For Each ws In ThisWorkbook.Worksheets
        For Each cb In ws.CheckBoxes
            cb.OnAction = "CheckBox_Click"
        Next
    Next
 
End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
 
    'This macro is called when any Checkbox is clicked.  Application.Caller is name of Checkbox that was clicked
 
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        If cb.Value = xlOn Then
            'The checkbox is Ticked - store current date in column O cell
            .Cells(cb.TopLeftCell.Row, "O").Value = Date
        Else
            'The checkbox is Unticked - clear column O cell
            .Cells(cb.TopLeftCell.Row, "O").ClearContents
        End If
    End With
 
End Sub

Put this code in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim ws As Worksheet
    Dim daysOld As Long
    Dim r As Long
 
    Set ws = Sh
    With ws
        'Set the number of days old depending on the current sheet name
        Select Case .Name
            Case "DAILY": daysOld = 1
            Case "WEEKLY": daysOld = 7
            Case "MONTHLY": daysOld = 30
            Case "QUARTERLY": daysOld = 90
            Case "YEARLY": daysOld = 365
        End Select
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If IsDate(.Cells(r, "O").Value) Then
                'A date is in column O cell, is it older than the specified number of days?
                If Date - .Cells(r, "O").Value >= daysOld Then
                    'Yes, so untick the checkbox in the same row by changing its linked cell in column A to False and clear the date in column O
                    .Cells(r, "A").Value = False
                    .Cells(r, "O").ClearContents
                End If
            End If
        Next
    End With
 
End Sub

Next, run the Set_OnAction_CheckBoxes_All_Sheets macro. You only need to run this macro once, or after you add new checkboxes. This macro assigns the CheckBox_Click macro to every checkbox on every sheet.

The idea is that when you click a checkbox the CheckBox_Click macro is run and, if the checkbox is ticked, it stores the date it was ticked in the column O cell, otherwise it clears the cell. And when you activate a sheet, the Workbook_SheetActivate event handler is run which looks at all checkboxes on the sheet and clears those which have a 'tick date' older than the specified number of days. More details are in the code comments.

Wow. I dont know how to thank you.
It is working like a charm <3