[VBA] Macro to Close Bento Orders in Excel | Auto-Lock by Date

Background

In an Excel file used to manage bento (lunch) orders, there was a specific need: “Prevent inputs in the next day’s order column after a certain time.”

Previously, this was done by manually locking the cells. To automate this, I created VBA code that automatically identifies “tomorrow’s date” and protects the corresponding column.

File Configuration and Specifications

This macro assumes the following file structure:

• D4 to AH4: Dates (Numeric values 1 to 31 representing the day of the month).
• C6 to C135: Employee names.
• D6 to AH135: Input area for orders (e.g., “Bento”, “x”).

When executed, the macro automatically locks only the column corresponding to tomorrow’s date, making it uneditable.

VBA Code

Below is the actual code used.

Sub CloseOrder()
    Dim tomorrowDate As Date
    Dim rng As Range
    Dim columnIndex As Integer
    Dim ws As Worksheet

    ' Get tomorrow's date
    tomorrowDate = Date + 1

    ' Specify the target sheet (Change "Sheet1" as needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Search for the column containing tomorrow's day (within D4:AH4)
    For Each rng In ws.Range("D4:AH4")
        ' Check if the cell value matches the day part of tomorrow's date
        If rng.Value = Day(tomorrowDate) Then
            columnIndex = rng.Column
            Exit For
        End If
    Next rng

    ' Highlight the column in yellow (Improve visibility)
    ws.Range(ws.Cells(6, columnIndex), ws.Cells(135, columnIndex)).Interior.Color = vbYellow

    ' Lock the specific column (Prepare for sheet protection)
    ws.Unprotect ' Unprotect temporarily to modify locked status
    ws.Range(ws.Cells(6, columnIndex), ws.Cells(135, columnIndex)).Locked = True
    ws.Protect   ' Re-protect the sheet to enforce the lock

    MsgBox "Orders for tomorrow have been closed."
End Sub

Notes and Key Points

• Date Format: The cells D4–AH4 must contain only the day number (e.g., 4, 15, 28) for Day(tomorrowDate) to match correctly.
• Protection Logic: even if the sheet is not currently protected, the Unprotect → Locked = True → Protect sequence ensures the lock is applied and enforced.
• Error Handling: If tomorrow’s date (e.g., the 32nd or a missing day) is not found in the header range, the code simply does nothing (skips to the end).

Summary

• Enables accurate deadline management by automatically locking the order column for tomorrow.
• Uses VBA to identify the correct date and process only that specific column.
• Includes a visual cue by highlighting the closed column in yellow.
• Improves operational efficiency by eliminating the need for manual sheet protection.

Implementing this macro ensures that the task of closing bento orders is performed reliably and easily.