Problem and Solution of Data Validation Not Working Copy Paste in Excel

Method 1 – Reason for Data Validation Not Working Copy Paste in Excel

Steps:

We selected Column B which contains the Employee Name.
From the Data tab, we selected Data Tools and finally selected Data Validation from there.

A dialogue box will appear.

The Settings tab is open in the dialogue box.
We selected Validation criteria from Allow. Here, I have chosen Text length.
Limit the range for validation. We 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.
Input data that does not satisfy the condition. We wrote the value Labuchange from the Waiting List.

A warning message will be shown for the invalid data entry. As I input data that was invalid according to the Data Validation condition, it did not accept the value, and a warning message appeared.

However, if you copy the value and paste it into the data validated column, it will accept it, and no warning message will appear.

This is a severe problem as our Data Validation is not working on copy-paste.

Method 2 – Creating Data Validation Using VBA to Work Copy Paste

Steps :

Select the Developer tab.
Select Visual Basic.

A new window will appear.

Click on the Sheet to which you want to apply the Code. We selected Sheet 2, VBA.

Select the Worksheet from General and Change from Declarations to create a Private Sub.

Input the following code on how you want to validate the data.

My used code is mentioned below:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        If Len(Target.Value) >= 8 Then
            MsgBox "Name is longer than 8. Undo!", vbCritical
            Application.Undo
        End If
    End If

End Sub

Check if the validation is working or not from the sheet.

We inserted the value from the D7 cell by copying and pasting it into B10. The value shows an Error alert according to the data validation condition. The warning box will appear.

This method also works perfectly if I input the data through the keyboard or any other process.

If you click OK, the cell will return to its previous mode.

Download Practice Workbook

Data Validation Not Working for Copy Paste.xlsm

<< Go Back to Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub CreateUniqueDropDownListInC12() Dim ws As Worksheet Dim cell As Range Dim dict As Object Dim dataValidation As Validation Dim dvList As String ' Define the worksheet where you want to apply the data validation Set ws = ActiveSheet ' Create a dictionary object to store unique values Set dict = CreateObject("Scripting.Dictionary") Application.ScreenUpdating = False ' Loop through the specified range (C5 to C11) For Each cell In ws.Range("C5:C11") If cell.Value <> "" Then ' Check for non-empty cells If Not dict.Exists(cell.Value) Then dict(cell.Value) = 1 ' Add the value to the dictionary End If End If Next cell ' Convert the unique values to a comma-separated string for data validation dvList = Join(dict.Keys, ",") ' Apply data validation with a dropdown list to cell C12 Set dataValidation = ws.Range("C12").Validation With dataValidation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dvList .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With Application.ScreenUpdating = True End Sub

11 Comments

yelnats Aug 13, 2022 at 2:26 AM

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 ?
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 9:16 AM
  
  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”.
Yelnats Aug 13, 2022 at 2:51 AM

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
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 9:17 AM
  
  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”.
Cole Oct 13, 2022 at 12:57 AM

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?
- Reply
  Sabrina Ayon Oct 18, 2022 at 10:35 AM
  
  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.
nishanth Nov 9, 2022 at 6:59 PM

Hey Cole, did the loop issue get sorted ? i am also facing the same issue !
Turan May 25, 2023 at 6:53 PM

Hi dear NAIMUL HASAN ARIF, this article is the great and very helpful. Thank you for it.

But I think that in your code have some misunderstanding in here “Private Sub Worksheet_SelectionChange(ByVal Target As Range)”.
I think it should be like that “Private Sub Worksheet_Change(ByVal Target As Range)”. Because when I click the cell of the range which I choose in VBA code, excel show error message in the VBA code.
- Reply
  Naimul Hasan Arif May 28, 2023 at 12:41 PM
  
  Dear TURAN,
  Thanks for your valuable comment. That was an honest mistake from me. I have updated the error. I have also modified the code as I have found a simpler way to perform the same task.
  
  Best Regards,
  Naimul Hasan Arif

Anas Nov 3, 2023 at 11:34 AM

What if I want to put data validation for duplicate values. Can I get the code for that ?

Joyanta Mitra Nov 5, 2023 at 11:19 AM

Dear Anas,
You have to write the following code to get data validation for duplicate values, you have write the code below.

Then we get the data values of unique departments Marketing and Sales.
datavalidation with duplicates
With regards,
Joyanta Mitra

Problem and Solution of Data Validation Not Working Copy Paste in Excel

Method 1 – Reason for Data Validation Not Working Copy Paste in Excel

Method 2 – Creating Data Validation Using VBA to Work Copy Paste

Related Articles

Leave a reply Cancel reply