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.