Forum Discussion

dhirajashet24's avatar
dhirajashet24
Copper Contributor
Mar 21, 2023

Disable copy paste function without VBA code

Hello, need some guidance in Excel. We have standard Excel format in office and customer need to update the data in the same format however they overwrite the drop down list by copy pasting the data ...
excel
Formulas and Functions
Marthurvin's avatar
Marthurvin
Copper Contributor
Nov 01, 2024
Unfortunately, all of the steps mentioned I'd already implemented. I discovered that the actual issue was not her copy/pasting, but rather her clicking the bottom boundary of the cell and dragging it slightly. This was causing the cell to unmerge (despite it being protected and supposedly unable to have its formatting changed). When it unmerges like that, all references to the cell cease to exist. She knows the pw so she unprotects the sheet, remerges it, then tries to continue... but of course that doesn't cause the references to that cell to magically reappear. When it gets back to me, it *looks* fine b/c the spreadsheet is too big to eyeball the numbers, but when I actually go into the formulas her attempts to fix it have actually broken it further (and hidden the problem). I've gotten her to stop trying to fix it but the issue of her dragging that bottom boundary enough to un-merge it remains.
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Nov 02, 2024

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.

Resources