3

I’m preparing an Excel workbook for multiple users.

  • Data is in Tables (ListObjects).
  • Some columns use Data Validation.
  • Users paste data (Ctrl+V), which removes the validation in the target cells.

Constraints:

  • I can’t enforce “Paste values only.”
  • I can’t rely on macros (many users disable them).
  • Users must be able to insert rows.

Question: Is there any built-in, non‑VBA way to prevent paste operations from removing Data Validation, while still allowing users to insert rows?

What I’ve tried:

  • Applied Data Validation to entire table columns so new rows inherit it.
  • Protected the sheet and allowed: Insert rows, Delete rows, Sort, Use AutoFilter, Edit objects. Cells that users fill are unlocked.

Observed: Pasting still removes validation. With sheet protection users cannot insert rows, even though I allowed it.

System: Windows 11 and Office 365

I am grateful for your help!

Improve this question
2
  • Have you tried using Ctrl+Shift+V (Paste values) instead of Ctrl+V? Commented Sep 22, 2025 at 14:30
  • 1
    Thanks for your hint, but I am not the one filling the table. The table must be quite robust against stupid users :) Commented Sep 22, 2025 at 14:50

1 Answer 1

Reset to default
6

I don't think there is a solution that fits within your constraints.

You will likely need to force users to enable macros, allowing you to then enforce PasteValues.

Something like the following will prevent users without macros enabled from using/editing the workbook.

Const PW = "password"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Sheet As Worksheet
    For Each Sheet In Me.Worksheets
        Sheet.Protect PW
    Next Sheet
    Me.Protect PW
    Me.Save
End Sub

Private Sub Workbook_Open()
    Dim Sheet As Worksheet
    Me.Unprotect PW
    For Each Sheet In Me.Worksheets
        Sheet.Unprotect PW
    Next Sheet
End Sub
Improve this answer
1
  • 1
    I already feared that I might get this answer. Thanks a lot. Will try to comvince them to allow macros. Commented Sep 22, 2025 at 14:52

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.