Disable copy paste function without VBA code

Marthurvin 

Example: Protect Critical Cells and Restore Them if Altered

This Office Script example monitors a range or individual cells for any changes and restores them to their original state if they are edited. It is useful for protecting formulas and important values.

function main(workbook: ExcelScript.Workbook) {
  // Define the worksheet and the critical range to monitor
  let sheet = workbook.getWorksheet("Sheet1"); // Update "Sheet1" to your actual sheet name
  
  // Define a range of cells to protect
  let protectedRange = sheet.getRange("A1:A10"); // Adjust as needed
  
  // Define the correct values or formulas for each cell in the protected range
  // This is hardcoded here for simplicity but can be customized for each cell
  let expectedFormulas = [
    "=SUM(B2:B10)", "=AVERAGE(B2:B10)", "100", "200", "=B2*2", // Example formulas/values
    "", "", "", "", ""
  ];
  
  // Loop through each cell in the range and check for changes
  for (let i = 0; i < protectedRange.getCellCount(); i++) {
    let cell = protectedRange.getCell(i, 0); // Get cell in the range
    let expectedFormulaOrValue = expectedFormulas[i];

    // If the formula or value is incorrect, restore it
    if (cell.getFormula() !== expectedFormulaOrValue && cell.getValue() !== expectedFormulaOrValue) {
      // Reset the formula or value as needed
      if (expectedFormulaOrValue.startsWith("=")) {
        cell.setFormula(expectedFormulaOrValue); // Restore formula
      } else {
        cell.setValue(expectedFormulaOrValue); // Restore static value
      }
    }
  }

  // Inform the user that the protected range has been checked and any incorrect values have been restored
  workbook.getApplication().showAlert("Protected cells were checked, and any unintentional changes were corrected.");
}

This script:

• Monitors a specified range or list of cells.
• Restores each cell to its expected formula or value if any unauthorized changes are detected.

1. Creating a Separate Script to Apply and Remove Sheet Protections

While you can't use Office Scripts to password-protect a sheet directly, you can create an automated workflow in Excel for the web by adding options that reset certain areas or formulas on demand.

2. Using Data Validation with Office Scripts for Indirect Protection

Data validation rules combined with Office Scripts can help protect input cells by ensuring that only expected values are entered. For example, a script could periodically check cells against data validation rules or apply validation rules to prevent specific input types.

Limitations of Office Scripts vs. VBA for Protection

• No Password Protection: Office Scripts cannot set or enforce passwords for sheet protection.
• No Direct Interception of User Actions: Office Scripts lack event-driven triggers (e.g., “on change”) that automatically run in response to user actions.
• Only Available in Excel for the Web: Scripts must be run manually unless combined with Power Automate, which enables automation but with limitations in real-time responsiveness.

For highly sensitive or critical workbooks, traditional Excel protections and VBA remain stronger options in the desktop version of Excel.

Marthurvin 

Given this specific issue, where dragging the cell boundary inadvertently unmerges cells and breaks references, here are some additional strategies to try without relying on VBA, aimed at limiting damage from accidental unmerging and guiding the user away from such actions.

Here are some possible solution proposals that I have fished out from the AI. Maybe they will help you, although without VBA it is difficult to handle.

I haven't checked whether they work, so be careful when implementing them.

1. Switch from Merged Cells to “Center Across Selection” for Appearance

• As mentioned earlier, “Center Across Selection” is a safer alternative to merging cells since it won’t unmerge or break formulas when boundaries are clicked or dragged. This technique centers text across multiple cells without actually merging them:
• Select the cells where merged text should appear.
• Right-click > Format Cells > Alignment > Horizontal: Center Across Selection.
• This visually replicates merged cells but prevents the boundary from being drag-sensitive, reducing the risk of accidentally unmerging and breaking references.

2. Add a “Repair Snapshot” with Helper Cells for Formula References

• Since unmerging cells accidentally breaks formula references, consider adding hidden helper cells as a backup reference for crucial formulas. Create hidden helper cells with the same values/formulas as the merged cells that are frequently broken.
• When a key cell breaks, these hidden helper cells can make it easier to recover original references and re-establish formulas without having to inspect each one individually.

3. Use Conditional Formatting to Flag Broken or Unintentionally Changed Cells

• Highlight critical cells that may lose their reference if unmerged by using conditional formatting to flag any alterations:
• For instance, create a condition that highlights a cell if it no longer contains a formula or the expected reference value.
• Use formulas like =ISERROR(SEARCH("EXPECTED TEXT", A1)) to highlight cells if the expected value or formula has disappeared.

4. Lock and Protect Formula Sheets Separately and Link to the Input Sheet

• To reduce accidental edits in key cells, consider splitting the spreadsheet into a Data Entry Sheet and a Protected Formula Sheet. In this structure:
• Allow the user to enter data in one “Input” sheet only.
• Reference the input cells in a separate “Calculation” sheet, where critical formulas are located.
• Protect the Calculation sheet completely and keep the password confidential, allowing access only to those needing it for formula adjustments.
• This way, even if users accidentally alter something on the input side, it won’t break key formulas, as these are housed in a separate protected area.

5. Leverage Comments or Visual Guides to Discourage Dragging Boundaries

• If certain merged cells are prone to boundary dragging, consider adding a brief comment on hover or colored shading to reinforce that these cells should be left intact. For example:
• Add a light fill color or comment that discourages clicking or dragging in that area, which could help reduce accidents.
• While not a technical solution, this can reduce the likelihood of unintended modifications.

6. Develop a Consistent Backup and Recovery Workflow

• Since complete prevention may not be feasible without VBA, establishing a regular backup workflow can mitigate the impact of accidental changes. Using Excel’s version history (if working in OneDrive or SharePoint) or manually saving periodic copies can ensure a backup is available if something is accidentally broken.

Maybe, Office Scripts can help secure specific sheets or ranges in Excel for the web, though they work differently from VBA and currently offer more limited options for protection compared to desktop Excel. Office Scripts cannot fully "lock" a sheet or range in the traditional sense as VBA does (e.g., setting passwords and restricting access directly). However, you can achieve similar protections.