VBA & Macro Problems

How to Automatically Lock Excel Cells Based on Date and Time using VBA

Amos Gikunda Oct 22, 2026 869 views

Question details

The user needs a method to automatically lock specific Excel cells once a scheduled date and time passes to prevent further data entry.

Automatically Lock Excel Cells Based on Date and Time

Product: Excel
Device & OS: not provided
Scenario: Managing a football prediction league where participants submit entries. The prediction cells must become uneditable the exact moment a match begins.
Observed behavior: By default, cells remain editable unless manually locked and protected. A dynamic solution is needed to switch the cell status based on a real-time comparison with a schedule.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have a dedicated column containing the exact dates and times for your deadlines.

Solution 1Recommended

Use a VBA Macro to Automatically Lock Cells

Implement a VBA script that triggers upon opening the workbook or modifying a cell to compare the current time with the scheduled time, locking the cells if the deadline has passed.

Excel does not have a built-in formula function to dynamically change a cell's protection status. To achieve this, you must write a macro using Visual Basic for Applications (VBA).

Keep in mind that cell locking only takes effect when a worksheet is protected, so your VBA code will need to unprotect the sheet, lock the necessary cells, and then re-protect the sheet.

Unlock All Data Entry Cells

Select the cells where users will enter their predictions. Right-click and choose 'Format Cells'. Go to the 'Protection' tab, uncheck the 'Locked' box, and click 'OK'.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications window. In the Project Explorer on the left, double-click 'ThisWorkbook' or the specific sheet name where your data resides.

Enter the VBA Code

Write a 'Workbook_Open' event or 'Worksheet_Change' script. The script should loop through your deadline cells, compare them to the current time using 'Now()', and if the deadline has passed, set 'Range.Locked = True' for the corresponding entry cells.

Protect the Worksheet within VBA

Include the command 'ActiveSheet.Protect Password:="your_password"' at the end of your script to enforce the newly applied lock status.

Save and Test

Save your file as a Macro-Enabled Workbook. Close and reopen the workbook to test if the macro correctly locks cells for past deadlines.

Use a VBA Macro to Automatically Lock Cells

Security Consideration: VBA passwords can sometimes be bypassed by advanced users. If your data requires high security, relying entirely on Excel VBA might not be sufficient compared to server-based or strict file-permission setups.

Solution 2

Set Up Individual Workbooks with File Permissions

When dealing with multiple users (e.g., a 20-player league), separate user workbooks stored in a shared location provide better access control than relying on a single password-protected file.

Best Office Suite for Macros

Easily Manage Spreadsheets and VBA Macros with WPS Office

WPS Spreadsheet provides comprehensive support for VBA macros and worksheet protection. You can effortlessly write, test, and execute scripts to automatically lock your time-sensitive data exactly as you would in Microsoft Excel, all within a highly compatible interface.

1. Open the Developer Tab: Launch WPS Spreadsheet and navigate to the 'Developer' tab to access the built-in VBA Editor.
2. Insert Your Code: Write your time-checking macro within the 'ThisWorkbook' module to ensure it runs when the file opens.
3. Manage Worksheet Protection: Use the 'Review' tab to configure your 'Protect Sheet' settings, ensuring your automated cell locks execute securely and flawlessly.

Full compatibility with Microsoft Excel .xlsx and .xlsm formats.Robust built-in VBA editor to handle complex automation scripts.Easy-to-use worksheet protection and cell formatting features.Free and lightweight alternative for seamless data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my cells locking even after the VBA macro runs?

Cell locking only takes effect when the worksheet is protected. Ensure your VBA code includes a command to protect the sheet (e.g., ActiveSheet.Protect), or manually protect it via the Review tab.

Can users change their computer's clock to bypass the VBA time lock?

Yes, if the VBA code relies on the local system time (Now()), a user can manually change their system clock to bypass the restriction. For highly secure applications, consider a web-based solution or pulling the time from an external server using an API.

Does the time check macro run continuously in the background?

Standard event macros only run when triggered (such as when the workbook opens or a cell changes). To check the time continuously, you would need to use the Application.OnTime method to schedule the macro to run at specific intervals.

How do I secure a source workbook from being edited by other users?

You can set a 'Password to Modify' when saving the workbook under General Options. Additionally, rely on OS-level or cloud folder permissions to strictly limit who has write access to the shared directory.

Excel & Data Problems