VBA & Macro Problems

How to Automatically Lock and Unlock Excel Cells by Shift Time Using VBA

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs to automatically restrict data entry in an Excel workbook so that only the current shift can edit their assigned cells, while locking the previous shift's cells.

How to Automatically Lock and Unlock Excel Cells by Shift Time

Product: Excel
Device & OS: not provided
Scenario: Managing a shift-based workbook where editing permissions must dynamically change based on the current system time.
Observed behavior: Currently, standard features like conditional formatting only highlight cells but cannot enforce editing permissions. A VBA script or controlled process is required to update cell lock properties dynamically.

Before you start

Before configuring automated cell locking, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have a clearly defined schedule of shift start and end times ready to reference.

Solution 1Recommended

Use a VBA Macro to Automatically Lock and Unlock Cells

Since standard features like Conditional Formatting cannot enforce data validation or editing permissions, a VBA macro is the most effective way to lock cells based on the system time.

To enforce editing restrictions, you need a controlled process with worksheet protection. A typical design stores shift start and end times, checks the current system time, and updates each cell's Locked property before protecting the sheet.

Set up a shift schedule

Create a reference table on a hidden sheet containing your shift names, start times, and end times.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor, and insert a new Module from the Insert menu.

Write the time-checking script

Write a VBA script that captures the current system time using the Time function and compares it against your recorded shift schedule.

Unprotect the worksheet

In your script, include the command ActiveSheet.Unprotect "YourPassword" to temporarily remove protection so the macro can modify cell properties.

Modify the Locked property

Select the cell ranges assigned to the current shift and set their Locked property to False. Set the previous shift's ranges to True.

Re-protect the worksheet

Use ActiveSheet.Protect "YourPassword" to re-apply the worksheet protection. You can trigger this macro automatically using the Workbook_Open event.

Use a VBA Macro to Automatically Lock and Unlock Cells

Macro Security: Users will need to enable macros when opening the workbook for this automated locking mechanism to function properly.

Advanced Spreadsheet Management

Manage Shift-Based Workbooks with WPS Spreadsheet

WPS Spreadsheet provides robust support for macros, VBA, and worksheet protection, allowing you to seamlessly automate cell locking and unlocking based on time criteria.

1. Open your workbook: Launch WPS Spreadsheet and open your shift-based schedule document.
2. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'Visual Basic' to access the VBA editor.
3. Insert your VBA code: Paste your time-based cell locking VBA code into the relevant worksheet or module and test the macro.
4. Protect and save: Set up your worksheet protection passwords from the 'Review' tab and save the document as a Macro-Enabled file (.xlsm).

Highly compatible with Microsoft Excel (.xlsx and .xlsm) formats.Supports VBA macros for advanced automation and time-based triggers.Easy-to-use worksheet protection to prevent unauthorized edits.Lightweight and free alternative for comprehensive data management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Conditional Formatting to lock cells based on shift time?

No, Conditional Formatting can only change the visual appearance (like background color or font style) of a cell to highlight the active shift. It cannot enforce editing permissions or lock cells. You must use VBA alongside Worksheet Protection for that purpose.

How do I prevent users from stopping or viewing the VBA macro?

To prevent users from disabling or modifying the macro, you can lock the VBA project with a password. Go to the VBA editor, right-click your project, select 'VBAProject Properties', navigate to the 'Protection' tab, check 'Lock project for viewing', and set a password.

What happens if a user opens the workbook but doesn't enable macros?

If macros are disabled, the VBA script will not run, meaning the cells will remain in whatever locked or unlocked state they were in when last saved. To prevent this, you can hide all functional sheets and only display a 'Please enable macros' warning sheet until the VBA script runs and unhides the working sheets.

Excel & Data Problems