Disable copy paste function without VBA code

Marthurvin 

It sounds like you've put a lot of thought into how to keep your spreadsheet useful and safe from accidental changes. The situation you describe is common in shared Excel environments, especially when multiple users access it frequently. Here are some suggested solutions that might help you resolve the issues.

Although VBA would make this process more resilient, the following solutions provide relative approximations to prevent accidental unmerging. The Center Across Selection option is often the most effective immediate solution because it minimizes accidental unmerging of cells without significantly changing the user's input experience.

Here are some suggested solutions that might help you resolve the issues:

Minimize Merged Cells and Use Center Across Selection

1. Replace Merged Cells with “Center Across Selection”:

• Instead of merging cells (which users can accidentally unmerge by dragging edges), use the Center Across Selection option.
• Select the range that would normally be merged, go to Format Cells > Alignment > Horizontal, and choose Center Across Selection.
• This maintains the same centered visual appearance but doesn’t technically merge cells, so users can’t unmerge them accidentally. 

2. Advantages: This approach makes cells appear merged without the vulnerability to unmerging issues.

3. Limitations: You’ll still need to adjust some formatting and spacing, but this significantly reduces the risk of broken cell references due to accidental unmerging.

---------------------------------------------------------------------------------

VBA macros do not run in Excel for the web, OneDrive, or SharePoint directly, as the online version of Excel does not support VBA execution. However, there are a few ways to work around this by using VBA on the desktop version and then syncing or leveraging the online environment as much as possible. Here’s how you might approach it, considering your setup with OneDrive/SharePoint and multiple users:

1. VBA Desktop Automation with OneDrive/SharePoint Sync:

• Users will have to open the workbook in the desktop version of Excel, which supports VBA.
• The file is saved on OneDrive or SharePoint to facilitate collaboration and auto-sync.

2. Triggers and Syncs:

• VBA runs on the user’s local Excel instance, and changes made by VBA are saved to the shared OneDrive/SharePoint workbook.
• Once saved, these updates will be reflected for all users after they sync with the shared location.

3. Solution Outline for VBA in a Collaborative Environment:

• Below is a template VBA setup. It checks for specific conditions (e.g., preventing unmerging or validating data) when users open or interact with the file in the desktop version. Any changes are synced back to the cloud.

VBA Code Example for Desktop Excel with Sync to OneDrive/SharePoint

This example includes macros that trigger on workbook open and before closing, as well as protections to prevent unmerging cells and validate data entry.

1. Prevent Unmerging Cells and Protect Cells on Workbook Open

This code will reapply protection whenever the workbook is opened, which ensures that merged cells cannot be unmerged by accident:

Vba Code is untested backup your file first.

Private Sub Workbook_Open()
    ' Ensure protection is applied whenever the workbook is opened
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="YourPassword", UserInterfaceOnly:=True
        ' Ensure all editable cells are unlocked for users
        Dim cell As Range
        For Each cell In ws.UsedRange
            If cell.Locked = False Then
                cell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Formula1:="YourList" ' Update "YourList" with actual validation criteria
            End If
        Next cell
    Next ws
    MsgBox "This workbook is protected. Any attempt to unmerge cells will be restricted.", vbInformation
End Sub

2. Data Validation Check Before Closing the Workbook

This code checks the cells with data validation before closing the workbook to ensure no invalid data was pasted:

Vba Code is untested backup your file

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim cell As Range
    Dim isValid As Boolean
    
    ' Loop through each sheet
    For Each ws In ThisWorkbook.Worksheets
        ' Check all cells with data validation
        For Each cell In ws.UsedRange
            On Error Resume Next
            isValid = cell.Validation.Type <> xlValidateCustom ' Avoid error if no validation
            On Error GoTo 0
            If Not isValid Then
                MsgBox "Invalid data detected in cell " & cell.Address & ". Please correct before closing.", vbCritical
                Cancel = True ' Prevent closing until fixed
                Exit Sub
            End If
        Next cell
    Next ws
    
    ' Save changes to OneDrive/SharePoint
    ThisWorkbook.Save
    MsgBox "Workbook saved and validation passed.", vbInformation
End Sub

 3. Sync Instructions for Users

Users will need to open the workbook in Excel’s desktop app by selecting Edit in Desktop App from the OneDrive or SharePoint web interface. Here’s what they should do:

• Step 1: Open the workbook directly from OneDrive/SharePoint in Excel Desktop (not Excel for Web).
• Step 2: Work within the Excel desktop environment with VBA code running and protecting cells.
• Step 3: Ensure changes are saved frequently. Upon save, changes will sync back to OneDrive/SharePoint, making them accessible to others.
• Step 4: Users must close the workbook completely to release it for others to edit, as Excel might lock files if multiple users attempt to edit the same file on the desktop simultaneously.

Important Considerations

• One-at-a-Time Editing: Only one user should open and edit the workbook in the desktop app at a time to avoid version conflicts, as Excel does not support co-authoring with VBA.
• Training and Instructions: Train users to edit only in the desktop app and save changes frequently to avoid sync conflicts.
• Alternative Solutions: Consider using Excel Online with Power Automate, as it enables multi-user editing without VBA but still allows for some automation (although this may involve setup in Microsoft Forms or external data entry).

Using the above setup, your VBA code can enforce protection and validation rules whenever the workbook is used in the desktop version, and OneDrive or SharePoint will handle the file sync automatically for each user after they close the workbook. This setup provides a workable compromise for complex Excel files shared over OneDrive or SharePoint but used with desktop VBA capabilities.

Amila_Rambukwella 

The code I provided wouldn’t be fully functional as expected because of the following limitations in Office Scripts:

1. Lack of Real-Time Event Triggers: Office Scripts currently cannot intercept changes immediately as they happen. So, even though the script checks for cells with invalid data, it can't react automatically when a user pastes data into a cell. Users would need to manually run the script after pasting to detect and clear invalid entries, which is far from ideal.
2. Data Validation Limitations: Office Scripts don’t have direct access to check if a cell's value matches its data validation rules in the way VBA can. While the script above tries to use a validation method (getRule()?.validate(cell.getValue())), this functionality isn't directly available in Office Scripts. Therefore, the validate function call wouldn't actually work.
3. Inability to Prevent Paste Actions: Since there is no way to directly disable pasting or drag-and-drop actions in Office Scripts, the script cannot proactively block users from pasting into protected cells.

What Can Be Done

The only feasible approach within Office Scripts for now is to:

• Manually Run the Script: Users would need to run this script manually (from the Automate tab in Excel for the web) to check and reset cells with invalid values periodically.
• Clear Invalid Values: The script can still clear any values that do not meet validation criteria, but this requires users to remember to run it.

Alternative Approach

If Excel for Desktop with VBA is an option, VBA would be much better suited to monitor paste actions in real-time, prevent data validation violations, and lock or unlock specific ranges dynamically. Store the workbook with VBA code in SharePoint or OneDrive. When a user opens the workbook in Excel Desktop, they can run the VBA code with full functionality.