Disallowing Pasting capabilities in Specific columns based on selected Data Validation while still enabling pasting in the same Data validation column

Question

First off let me start by saying I am very new to any form of Coding, VBA is my first attempt at learning it and I am not learning it quickly.

My problem is I have a sheet that I have the intention of giving to vendors for them to fill our product information. I provided drop downs and tried to create some formulas to reduce the work that the vendor had to do, while also controlling what data I get back makes sense. Unfortunately, the vendors decided to copy and paste overtop of the Drop down options which override the data validation, making the efforts I initially put in redundant, while simultaneously removing any form of data standardization.

I would like to create some code that will enable me to restrict the ability to copy and paste cells that

1. Do not contain data validation
2. is a different type of Data validation than what is assigned to that column
3. maintains copy and pasting of the cells within a specific column into the same column ex. Column E:E has 3 values that can be selected from (red, blue, yellow). Column G:G has 5 values that can be selected from (Volkswagen, Volvo, MINI, Mercedes, Jaguar) I would like E:E to be able to past inside of column E but not into Column G even though both have data validation.

I followed a thread that was previously made a long time ago:

INITIAL SOURCE THREAD

SECONDARY SOURCE THREAD

In the secondary Source thread, there is a discussion that helps describe my exact challenge but does not lead anywhere in the comments of the Answer

Below is the Code I am using

`Dim boolDontShowAgain As Boolean

 Private Sub Worksheet_Change(ByVal Target As Range)
     On Error GoTo Whoa

      Application.EnableEvents = False

 'Does the validation range still have validation?
 If Not HasValidation(Range("PIM - MASTER DATA!A3:A999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!G3:G999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!H3:H999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!I3:I999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!O3:O999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!P3:P999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!Q3:Q999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!R3:R999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!S3:S999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!R3:R999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!AF3:AF999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!AG3:AG999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!BG3:BG999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!BH3:BH999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!BR3:BR999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!BS3:BS999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!CG3:CG999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!CH3:CH999")) Then RestoreValidation
 If Not HasValidation(Range("PIM - MASTER DATA!CI3:CI999")) Then RestoreValidation

 Letscontinue:
     Application.EnableEvents = True
     Exit Sub
 Whoa:
     MsgBox Err.Description
     Resume Letscontinue
 End Sub

 Private Sub RestoreValidation()
     Application.Undo
     If boolDontShowAgain = False Then
         MsgBox "Your last operation was canceled." & _
         "It would have deleted data validation rules.", vbCritical
         boolDontShowAgain = True
     End If
 End Sub

 Private Function HasValidation(r) As Boolean
     On Error Resume Next
     Debug.Print r.Validation.Type
     If Err.Number = 0 Then HasValidation = True
 End Function`

Answer 1

Try below code, the conditions for columns are set to 1 (A) and 5 (E), and not trigger for row 1 (cell.Row > 1). Adjust as needed for your specific columns and rows.

Dim AllowUndo As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    On Error GoTo ErrorHandler
    
    Application.EnableEvents = False

    ' Check if undo operation is allowed
    If AllowUndo Then
        AllowUndo = False
        GoTo ExitProcedure
    End If
    
    For Each cell In Target
        If (cell.Column = 1 And Not HasValidation(cell) And cell.Row > 1) Or _
           (cell.Column = 5 And Not HasValidation(cell) And cell.Row > 1) Then
            GoTo RestoreValidation
        End If
    Next cell

ExitProcedure:
    Application.EnableEvents = True
    Exit Sub

RestoreValidation:
    MsgBox "Your last operation was canceled." & _
           "It would have deleted data validation rules.", vbCritical
    
    ' Enable undo operation
    AllowUndo = True
    Application.EnableEvents = True
    Application.Undo
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume ExitProcedure
End Sub

Private Function HasValidation(r As Range) As Boolean
    On Error Resume Next
    If Err.Number = 0 Then HasValidation = Not IsEmpty(r.Validation.Type)
End Function