VBA & Macro Problems

How to Lock Cells Beneath a Shape Using VBA in Excel

Phi Hung Vo Oct 21, 2026 868 views

Question details

The user needs a VBA macro to identify the rectangular range of cells located beneath an Excel shape, lock only those specific cells, and protect the worksheet while keeping the rest of the sheet editable.

How to Lock Cells Beneath a Shape Using VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a protected worksheet where specific regions covered by shapes are dynamically locked using a VBA macro.
Observed behavior: Cells within the shape's bounding box are locked and protected, preventing user edits, while all surrounding cells remain unlocked and editable.

Before you start

Before running the macro, ensure that your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled on your ribbon to access the VBA editor.

Solution 1Recommended

Use VBA to Lock the Bounding Cells Under a Shape

Identify the shape's bounding box using the TopLeftCell and BottomRightCell properties, apply the lock property to this specific range, and protect the sheet.

Excel defines the area of a shape through its rectangular bounds rather than its exact irregular outline. By utilizing the TopLeftCell and BottomRightCell properties in VBA, you can isolate the specific range covering the shape.

The following macro will first unlock all cells on the active sheet, lock only the cells directly beneath the designated shape, and finally apply worksheet protection so the lock takes effect.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications editor. Go to the top menu and click Insert > Module to create a new module.

Insert the VBA Code

Copy and paste the following code into the module window: ```vba Sub LockCellsUnderShape() Dim ws As Worksheet Dim shp As Shape Dim covered As Range Set ws = ActiveSheet Set shp = ws.Shapes("Shape1") Set covered = ws.Range(shp.TopLeftCell, shp.BottomRightCell) ws.Cells.Locked = False covered.Locked = True ws.Protect Contents:=True, DrawingObjects:=True End Sub ```

Modify the Shape Name

Change "Shape1" in the code to match the actual name of the shape you want to lock the cells underneath. You can find the shape's exact name in the Name Box (top left of the formula bar) when the shape is selected.

Run the Macro

Press F5 or click the green Run button in the toolbar to execute the macro. The cells beneath your shape will be locked, and the worksheet will be protected.

Worksheet Protected Successfully: Users can now freely edit any cell outside the shape's boundary, but attempting to edit the cells beneath the shape will trigger a protection warning.

Solution 2

Fill or Highlight Cells Beneath a Shape Using VBA

If you need to visually identify or insert values into the cells under a shape instead of locking them, you can loop through the bounded range and apply a fill color or value.

Advanced Spreadsheet Capabilities

Run Macros and Lock Cells Easily with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly execute scripts that interact with shapes, lock cells, and protect your worksheets just like in Microsoft Excel.

1. Enable Macros in WPS: Open WPS Spreadsheet, navigate to the Developer tab on the top ribbon, and ensure macros are enabled.
2. Launch the VBA Editor: Click on 'Visual Basic' within the Developer tab to open the built-in VBA editor and click Insert > Module.
3. Paste and Execute Code: Paste your shape-locking VBA script into the module, update the shape name, and press F5 to lock the underlying cells instantly.

Fully compatible with Microsoft Excel (.xlsm) macro-enabled formats.Built-in VBA editor to easily write, run, and debug your VBA macro scripts.Free, lightweight, and fast alternative to heavy spreadsheet programs.

microsoft office alternative - wps office

Frequently Asked Questions

Can I lock the cells that match the exact irregular outline of a shape?

No, Excel VBA only exposes a shape's bounding box via the TopLeftCell and BottomRightCell properties. It locks the entire rectangular grid area covering the shape, not just the individual pixels of an irregular shape like a star, circle, or triangle.

How do I find the exact name of the shape for my macro?

Click on the shape in your worksheet, then look at the Name Box located to the left of the formula bar. You can copy the exact text from there and replace 'Shape1' in your VBA code.

Will running this macro delete data in the cells underneath the shape?

No, locking the cells only prevents users from editing them while the worksheet is protected. Any existing data, formatting, or formulas within those cells will remain completely intact.

Why am I getting a 'Subscript out of range' error when running the macro?

This error usually occurs if the shape name specified in the VBA code (e.g., 'Shape1') does not exist on the active worksheet. Ensure the shape name in the code exactly matches the name shown in the Name Box when the shape is selected.

Excel & Data Problems