Excel Data Validation is an amazing feature that controls the data entered into the worksheet. In the time of entering new data, we can set any kind of condition according to our need on selected cells using Excel Data Validation. But usually, Excel Data Validation Not Working Copy Paste is a major problem in this procedure.
For more clarification, I am going to use a dataset of a company containing data of Employee Name, Department and employees who are in Waiting List.
Download Practice Workbook
Problem and Solution of Data Validation Not Working Copy Paste in Excel
1. Reason for Data Validation Not Working Copy Paste in Excel
In this dataset, I am going to employ the Data Validation Feature in the Employ Name column to limit my entries.
Steps :
- For this, I have selected Column B which contains the Employee Name.
- Then, from the Data tab, I have selected Data Tools and finally selected Data Validation from there.
A dialogue box will appear.
- Here, the Settings tab is open in the dialogue box.
- After that, we have to select Validation criteria from Allow. Here, I have chosen Text length.
- Next, I have to limit the range for validation. Here, I have allowed the data containing text from a minimum of 1 to a maximum of 8 characters for validation.
The Data Validation feature will be applied.
Followingly, I input data that does not satisfy the condition. Here, I wrote the value Labuchange from the Waiting List.
A warning message will be shown for the invalid data entry. As I input data that is invalid according to the Data Validation condition, it did not accept the value and a warning message has appeared.
But if you copy the value and paste it in the data validated column, it will accept the value and no warning message will appear.
This is a serious problem as our Data Validation is not working on copy paste.
Read More: Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
Similar Readings
- How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
- Use Data Validation in Excel with Color (4 Ways)
- How to Use Data Validation List from Another Sheet (6 Methods)
- Excel VBA to Create Data Validation List from Array
- How to Use Named Range for Data Validation List with VBA in Excel
2. Creating Data Validation Using VBA to Work Copy Paste
In the case of solving the excel Data Validation Not Working for Copy Paste problem, Visual Basic for Applications (VBA) is the only solution. We are going to explain the solution here.
Steps :
- First of all, select the Developer tab.
- Next, select Visual Basic.
A new window will appear.
- Click on the Sheet in which you want to apply the Code. Here, I selected Sheet2 named VBA to apply the code.
- Select the Worksheet from General and Change from Declarations to create a Private Sub.
- Now, input the following code on how you want to validate the data.
My used code is mentioned below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidatedCells As Range
Dim Cell As Range
Set ValidatedCells = Intersect(Target, Target.Parent.Range("B:B"))
If Not ValidatedCells Is Nothing Then
For Each Cell In ValidatedCells
If Not Len(Cell.Value) <= 8 Then
MsgBox "The Name """ & Cell.Value & _
""" inserted in " & Cell.Address & _
" in column B was longer than 8. Undo!", vbCritical
Application.Undo
Exit Sub
End If
Next Cell
End If
End Sub
Here, I created a Private Sub name Worksheet_Change, I declared two variables these are ValidatedCells and Cell as Range. Next, I used the Set method to keep the range where I want to apply validation.
Then, selected Column B for validation. I also mentioned the range here using the Range method. I used the For loop in the nested IF statement to set the conditions Text Length of the selected range can’t be longer than 8. If it does not match the range, a warning box will appear to show a message through MsgBox and Undo option will be available.
- Now, Save the code.
- Then, check is the validation is working or not from the sheet.
Here, I insert the value from the D7 cell by copying and pasting it in B10. The value shows an Error alert according to the data validation condition. So, the warning box will appear.
This method also works perfectly if I input the data through the keyboard or any other process.
Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel
Practice Workbook
You can practice here for being an expert.
Conclusion
Excel Data Validation Not Working Copy Paste problem can have a severe impact in so many important cases. I hope you will be benefited from the solution. For any further questions related to the topic, comment below.
Related Articles
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
- How to Create Excel Drop Down List for Data Validation (8 Ways)
- Data Validation Drop Down List with VBA in Excel (7 Applications)
- Autocomplete Data Validation Drop Down List in Excel (2 Methods)
- Excel Data Validation Drop Down List with Filter (2 Examples)
Great Help so far!
What If I want Column A and B to have limit character of 8, but column C a limit of 10.
How would you do it ?
Thanks for the appreciation.
For columns A & B:
From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.
For column C, follow the same procedure. Just in the maximum section, input “10”.
Hello,
What if I want column A to have a limit of 8 and Column B to have a limit of 10. How would I do that ?
Thanks
Hi,
For columns A:
From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.
For column B, follow the same procedure. Just in the maximum section, input “10”.
Hello, I have follwed the steps above and am running into an issue where the error message continues to “loop” and doesn’t let me change the text that is over the character limit. Is there a way to let a user go back and edit after the error message appears?
Hello, COLE!
Thanks for sharing your problem with us!
Can you please send me your Excel file at [email protected]? So that, I can help you.
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.
Hey Cole, did the loop issue get sorted ? i am also facing the same issue !