Lock cells value can't be edited but can be deleted

Aspose.Cells Product Family
1

Hi, I have some cells containing formulas that I don’t want users to modify. I’ve locked the cells and protected the sheet, which prevents editing. However, users can still delete the values in those cells. Is there a way to prevent both editing and deletion of the cell contents?

I’m using Aspose.Cells.GridJS

2

@abdelaspose

Preventing Both Editing and Deleting of Formula Cells in Aspose.Cells.GridJS

When you protect a worksheet and set a cell’s Locked flag, Excel (and GridJS) will block any direct edit of that cell.
However, the Delete key (or a program‑matic call to setValue(null)) can still clear the cell’s content if the sheet protection does not forbid “clear‑contents” operations.

Below is a complete, step‑by‑step solution that works for Aspose.Cells.GridJS:

  1. Mark the formula cells as locked
  2. Protect the worksheet with the appropriate protection options (disable “Clear contents”, “Delete rows/columns”, etc.).
  3. (Optional)‑ Add a client‑side guard so that any attempt to change a locked cell via the GridJS UI is cancelled.

:one: Lock the cells that contain formulas

// assume `workbook` is an instance of Aspose.Cells.GridJS.Workbook
var sheet = workbook.getWorksheets().get(0);   // first sheet
var range = sheet.getRange("B2:D10");         // cells with formulas

// lock every cell in the range
for (var r = range.getFirstRow(); r <= range.getLastRow(); r++) {
    for (var c = range.getFirstColumn(); c <= range.getLastColumn(); c++) {
        var cell = sheet.getCell(r, c);
        cell.getStyle().setLocked(true);   // <-- this is the key flag
    }
}

Why?
In GridJS a cell is editable only when both the worksheet is unprotected or the cell’s Locked property is false. Setting Locked = true tells the protection engine to treat the cell as read‑only.

:two: Protect the worksheet and disable “clear‑contents”

// protect the sheet with a password (optional)
var protection = sheet.getProtection();
protection.setPassword("MySecretPwd");

// ---------- Restrict actions on protected sheets ----------
protection.setAllowDeletingRows(false);
protection.setAllowDeletingColumns(false);
protection.setAllowEditingObject(false);
protection.setAllowEditingScenario(false);
protection.setAllowFormattingCells(false);
protection.setAllowFormattingColumns(false);
protection.setAllowFormattingRows(false);
protection.setAllowInsertingRows(false);
protection.setAllowInsertingColumns(false);
protection.setAllowSelectLockedCells(true);   // allow selection (read‑only)
protection.setAllowSelectUnlockedCells(true);

// IMPORTANT: this flag stops the **Clear Contents** command
protection.setAllowDeleteRows(false);         // prevents row‑level delete
protection.setAllowDeleteColumns(false);      // prevents column‑level delete

// finally, enable protection
protection.setProtected(true);

The setAllowDeleteRows/Columns flags are the GridJS equivalents of Excel’s “Delete rows/columns” protection option. When they are false, the UI will also block the Delete key (which internally tries to clear the cell).

:three: Optional – Client‑side safeguard (recommended for custom UI)

If you expose a toolbar or allow JavaScript code to change cell values directly, you should also listen to the CellChanging event and cancel the operation when the target cell is locked.

workbook.on("cellChanging", function (e) {
    var cell = e.cell;               // Aspose.Cells.GridJS.Cell
    var style = cell.getStyle();

    // If the cell is locked, reject the change
    if (style.getLocked()) {
        // Cancel the operation
        e.cancel = true;

        // (Optional) show a friendly message
        alert("This cell contains a protected formula and cannot be modified or cleared.");
    }
});

The event handler runs before any value is written, so even a scripted call such as cell.setValue(null) will be blocked.

:pushpin: Summary Checklist

Step Action GridJS API
1 Set Locked = true on every formula cell cell.getStyle().setLocked(true);
2 Protect the worksheet and disable delete/clear operations sheet.getProtection().setProtected(true); + related setAllow* flags
3 (optional) Cancel any programmatic edit of locked cells workbook.on("cellChanging", …);

After these steps:

  • Users can select the protected cells (read‑only) – useful for review purposes.
  • The Delete key, right‑click → Clear Contents, or any JavaScript call that tries to wipe the cell will be rejected.

:link: Related Aspose Documentation

If you run into any issues while implementing the above code (e.g., version‑specific API differences), please let us know:

  • Which version of Aspose.Cells.GridJS you are using.
  • Whether the problem occurs in the UI, via the API, or both.

We’ll be happy to fine‑tune the solution for your exact environment.

3

@abdelaspose
It shall keep same behavior as in MS-Excel.
Can you provide a sample Excel file?

4

Hi Peter,
You can try editing and deleting cell B19.
DeletableLockedCells.zip (81.7 KB)

5

@abdelaspose
By using sample file for testing, we were able to reproduce the issue. Lock cells value can’t be edited but can be deleted.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSGRIDJS-1895

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.