Prevent Pasting into Excel Data Validation Cells with VBA in WPS Office
Question details
The user needs to prevent pasting over data validation drop-down cells in an Excel template with restricted ranges B2:B5000, E2:E5000, F2:F5000, J2:J5000, K2:K5000, and M2:M5000.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An Excel template contains data validation drop-down lists across several large ranges, and the author wants to stop users from overwriting those validated cells by pasting.
- Observed behavior
- Users can currently paste values into the data validation cells and overwrite the drop-down choices; a Worksheet_Change VBA routine is needed to undo the paste and warn them.
Open the workbook in WPS Office Spreadsheets, press Alt+F11 to open the VBA editor, and confirm that the template ranges B2:B5000, E2:E5000, F2:F5000, J2:J5000, K2:K5000, and M2:M5000 contain the data validation lists before adding the macro.
Block Paste Operations on Data Validation Cells with a Worksheet_Change Macro in WPS Office
Use this when you need to keep drop-down validation intact across large template ranges and reverse accidental or intentional multi-cell pastes immediately.
This method works because the Worksheet_Change event fires after cells are changed. The routine checks whether any changed cells intersect the protected validation ranges, tests whether those cells have data validation, undoes the entire paste, and then warns the user. WPS Office Spreadsheets supports this VBA event when the code is placed in the worksheet module.
In WPS Office Spreadsheets, right-click the worksheet tab that contains the validation ranges and choose View Code. You can also press Alt+F11 and double-click the sheet name in the Project pane.
Inside the sheet module, paste a Worksheet_Change routine that uses Intersect(Target, Me.Range("B2:B5000,E2:E5000,F2:F5000,J2:J5000,K2:K5000,M2:M5000")) to detect changes in the restricted columns, then loops through Intersect(Target, validationRange) and tests each cell for data validation.
Within the same routine, wrap Application.EnableEvents = False before Application.Undo, display a MsgBox warning such as "Pasting into data-validation cells is not allowed.", and set Application.EnableEvents = True again in the error-handling exit.
Save the workbook as a macro-enabled file, then test editing a normal cell, editing a validated cell, and pasting into multiple validated cells to confirm the restricted paste is undone and the warning appears.
Use WPS Office Spreadsheets to Run the Paste-Blocking VBA Macro
Open the same Excel template in WPS Office Spreadsheets, add or run the Worksheet_Change macro in the sheet module, and let the routine undo unauthorized pastes while showing a warning. WPS Office supports VBA-enabled workbooks and preserves data validation behavior.
- 1. Open the template: Launch WPS Office Spreadsheets and open the macro-enabled Excel template that contains the validation ranges.
- 2. Open the sheet module: Right-click the worksheet tab and select View Code to open the VBA editor for that sheet.
- 3. Insert the Worksheet_Change code: Paste the Worksheet_Change event macro that checks the restricted ranges, calls Application.Undo, and displays a warning while Application.EnableEvents is disabled.
- 4. Save as macro-enabled workbook: Save the file in .xlsm format so the VBA project and macro remain available.
- 5. Test the behavior: Try typing in a validated cell and pasting a value into B2 or E2 to confirm only unauthorized paste actions are undone.
Frequently Asked Questions
Why must the VBA code go in the worksheet module instead of a standard module?
Worksheet_Change is a built-in sheet event, so it fires only when placed in the relevant worksheet module. In WPS Office Spreadsheets, right-click the worksheet tab and choose View Code to open that module.
How do I stop the macro from running while I edit the template myself?
Set Application.EnableEvents = False in the Immediate window before editing, then set it back to True after you finish. You can also temporarily comment out the Worksheet_Change code.
What happens when a user pastes over multiple validated cells at once?
The Intersect check covers all Target cells in the restricted ranges. Use Application.Undo to reverse the entire paste and display the warning, then re-enable events in the error-handling exit.
Does this work if the workbook is saved as .xlsx?
No, VBA macros require a macro-enabled format. Save the workbook as .xlsm in WPS Office Spreadsheets so the Worksheet_Change event is retained.
Can I protect the validation cells without VBA?
You can unlock other cells and protect the worksheet, but sheet protection may block normal editing. The VBA method allows normal edits while blocking only pastes into validated ranges.
