I have one Excel sheet which contains many drop-down columns. Let's say one column has Test1, Test2, and Test3 as drop-down option values. Cells under this column should not accept the values other than these three. If I copy and paste 'XYZ' into these cells it should not accept. Can anyone help me how to handle this?
2 Answers
Set up Data Validation
You will want to use the Data Validation feature in Excel to restrict cells to specific text. In your case: "Test1", "Test2", and "Test3"
Select the cells that should only accept the values "Test1", "Test2", and "Test3"
On the Data tab, in the Data Tools group, click the Data Validation button.
In the Allow: drop-down, select List
In the Source: field, enter Test1,Test2,Test3
Click OK to apply changes.
At this point, every cell you selected in Step 1 will have a drop-down list associated with it. Users of the spreadsheet will only be able to enter Test1, Test2, or Test3. If they try to enter anything else, they will get the following error message:
You Can customize the error message text, if desired, by clicking on the Error Alert tab in Step 4 from above.
Prevent Copy/Paste over Data Validated cells
This will work great, EXCEPT in the case where someone pastes content over the top of the cells. The only way to prevent that from happening is to write a macro that detects updates to the cells, determines if data validation is enabled on the cells, and then denies the paste if true.
- Select all the cells that have data validation and name them DataValidationRange. You can name ranges of cells by typing the name to the left of the formula bar, where the Cell address is located.
- In the code module for the worksheet (Sheet1, for example), add the following code:
Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(Range("DataValidationRange")) Then Exit Sub Else Application.Undo MsgBox "Error: You cannot paste data into these cells." & _ "Please use the drop-down to enter data instead.", vbCritical End If End Sub Private Function HasValidation(r) As Boolean 'Returns True if every cell in Range r uses Data Validation On Error Resume Next x = r.Validation.Type If Err.Number = 0 Then HasValidation = True Else HasValidation = False End Function
Unfortunately, VBA is the only way to prevent someone from pasting over the cells. This has been an issue with Data Validation in Excel since the beginning. Hope it helps either way.
-
Hi Tyler, Thank you for your help. It's working fine if I type or double click the cell and paste the invalid value. But it is not giving any error message if I click(single) on cell and paste. Feb 6, 2015 at 6:38
-
@Charan I updated the answer with the VBA code required to deny the paste. This cannot be done without VBA unfortunately.– user387876Feb 6, 2015 at 19:32
-
I tried your code (in Excel 2013), and the
Application.Undo
statement seems to cause a recursive call toWorksheet_Change
. Jun 20, 2021 at 3:49
A simpler solution would be to just disable pasting into the workbook entirely. I just found the solution for that here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
-
1
-
1
-
Burning down your house is a ‘‘simple’’ way to prevent termites. Jun 19, 2021 at 19:30