How to Create “Locked Until Complete” Checklists Using Excel Logic and Sheet Protection

 

A “locked until complete” checklist empowers task management. It ensures tasks are completed sequentially, improving organization and accountability. You can create such a type of checklist by combining Sheet logic, VBA, and sheet protection.

In this tutorial, we will show how to create a “locked until complete” checklist using Excel logic and sheet protection.

Step 1: Set Up Your Checklist

Let’s create a task list clearly in Excel.

Step 2: Insert Checkboxes and Link Cells

• Go to the Developer tab >> select Insert >> select Checkbox from Form Control.

• Place checkboxes beside each task under the “Completed” column.
• Right-click each checkbox >> select Format Control.

• Select the Cell link each checkbox to the corresponding cells under the “Status” column (e.g., D2, D3, D4, D5, D6, D7).

• Click OK.

Step 3: Use Excel Sheet Logic and VBA to Lock/Unlock Tasks

Since Excel’s Data Validation doesn’t apply to Form Controls, use VBA to control checkbox states based on logic:

• Go to the Developer tab >> select Visual Basic.
• From the Insert menu >> select Module.

• Copy and paste the following code.

Sub CheckBoxControls()
    
    ActiveSheet.CheckBoxes("Check Box 2").Enabled = Range("D2").Value
    ActiveSheet.CheckBoxes("Check Box 3").Enabled = Range("D3").Value
    ActiveSheet.CheckBoxes("Check Box 4").Enabled = Range("D4").Value
    ActiveSheet.CheckBoxes("Check Box 5").Enabled = Range("D5").Value
    ActiveSheet.CheckBoxes("Check Box 6").Enabled = Range("D6").Value

End Sub

• This VBA code enables or disables Check Boxes based on the cell values.
  • If D2 is TRUE, the checkbox becomes enabled (clickable).
  • If D2 is FALSE, the checkbox is locked.
  • This logic ensures Task 2 can’t be clicked until Task 1 is completed.
• Replace “Check Box 2” and “Check Box 3” with your actual checkbox names.
• To find checkbox names, right-click and check the name box above column A.
• For better understanding, we haven’t removed or edited the checkbox names.

Step 4: Assign Macro to Checkboxes

• Right-click each checkbox >> select Assign Macro.

• Select CheckBoxControl >> click OK.

• This ensures the macro runs whenever a checkbox is clicked.

Step 4: Conditional Formatting for Visual Cue

You can visually highlight the locked/unlocked cells.

• Select the “Task Description” column.
• Go to the Home tab >> select Conditional Formatting >> select New Rule.
• Select Use a formula to choose which cell to format.
• Insert the following formula:

=$D2=FALSE

Where D2 is again the linked status cell of the previous task.

• Set a formatting style. To show a Grayed-out vibe, select a gray color font for locked tasks.
• Click OK.

Now, tasks visually indicate their locked/unlocked status.

Step 5: Apply Sheet Protection

To secure your logic:

• First, unlock cells where user interaction is allowed:
• Select the checkbox cells.
• Right-click >> select Format Cells.

• Go to the Protection tab >> uncheck Locked.
• Click OK.

Now, your sheet is protected, and users can only interact with the unlocked checkboxes.

To enforce structure and logic:

• Go to the Review tab >> select Protect Sheet.
• Set a password if you want.
• Uncheck the “Edit Objects” option, then click OK.

Step 6: Test the Checklist

• You can remove the Check box name or assign a new name.
• Initially, only Task 1 will be selectable.

• When Task 1 is checked (i.e., D2 becomes TRUE), Task 2 becomes enabled.
• This pattern will continue following the task order.
• You can’t select any task before completing the previous one.
• Try to check Task 6 without checking Task 5.
• Task 6 is showing as locked.

Adding Dynamic Progress Indicators

You can insert a dynamic progress indicator to show the progress of a task based on the checklist.

• Select a cell and insert the following formula.

=COUNTIF(D2:D7, TRUE)/COUNTA(D2:D7)

• Format the cell as a percentage to display the completion progress.

Final Thoughts and Use Cases

By following the above steps, you can create powerful “Locked Until Complete” checklists that promote task discipline. It helps to improve task management by enforcing sequential task completion. You can use this technique in the following fields;

• Training checklists: Employees must complete specific training steps sequentially.
• Safety procedures: Ensuring critical steps are not skipped.
• Quality checks: Maintaining compliance and standards.
• Educational course modules: Students must complete the pre-requisite to move further.