Avoid more than one Data validation from being overwrite

[a1b2c3d4]

I'm not much familiar with the VBA, however, do google search and edit to certain extent to customize as per my requirements. After tedious search, I was able to identify below code for avoiding data validation (DV) from being overwritten due to paste actions. But the challenge that I'm facing is I'm able to effect his DV only for one set of range and not multiple.
For date value I used the below code to avoid from being overwritten,

Private Sub Worksheet_Change(ByVal Target As Range)
'updateby Extendoffice 20150530
Set w = ActiveSheet.Range("Q2:S3001")
For Each c In w
If c.Value <> "" And Not IsDate(c) Then
c.ClearContents
MsgBox "Only a date format is permitted in this cell."
End If
Next c
End Sub

but, when I use different code which I got from this forum, it is getting executed at individual capacity, after I exclude the above code. The second code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim isect As Range
    Dim cell As Range
    Dim dd As Variant
    Dim i As Long
    Dim mtch As Boolean
    Dim msg As String
    Dim myEntries As String
    
'   See if any updated cells fall in E7:E12
    Set isect = Intersect(Range("E7:E12"), Target)
    
'   Exit if updated cells do not fall in E7:E12
    If isect Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
'   Set drop-down values
    dd = Array("apple", "banana", "cherry")
    
'   Loop through all intersecting cells
    For Each cell In isect
'       See if cell entry matches any drop-down values
        mtch = False
        For i = LBound(dd) To UBound(dd)
            If cell.Value = dd(i) Then
                mtch = True
                Exit For
            End If
        Next i
'       If value is not in list, erase and return message
        If mtch = False Then
            cell.ClearContents
            msg = msg & cell.Address(0, 0) & ","
        End If
    Next cell
    
'   Build string of validation entries
    For i = LBound(dd) To UBound(dd)
        myEntries = myEntries & dd(i) & ","
    Next i
    myEntries = Left(myEntries, Len(myEntries) - 1)
    
'   Reset validation
    With Range("E7:E12").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=myEntries
'        .IgnoreBlank = True
'        .InCellDropdown = True
'        .InputTitle = ""
'        .ErrorTitle = ""
'        .InputMessage = ""
'        .ErrorMessage = ""
'        .ShowInput = True
'        .ShowError = True
    End With
    
'   Return message, if necessary
    If Len(msg) > 0 Then
        MsgBox "Invalid entries in cells: " & vbCrLf & Left(msg, Len(msg) - 1), vbOKOnly, "ERROR!"
    End If


    Application.EnableEvents = True


End Sub

Both the above codes are not my creativity, this is something I searched from internet.


Now my concern is that I have multiple DV to be placed in same sheet in multiple columns, any help with some simple DV code, where I can customize for multiple columns.
Help on this is much appreciated.
Thanks in advance!

 

[a1b2c3d4]

Re: how to avoid more than one Data validation from being overwrite

First, I would like to apology for the delayed reply. Though, I read your questions on the day you posted but due to reporting week couldn't revert. Answers are as follows:

1. Which version of Excel are you using? I'm using excel 2010 version
2. Are your data validation cells individual cells or whole columns? I'm using data validation up to the range of 3000 cells in a column, likewise, there are multiple columns on which I'm applying validation concepts.
- if individual cells - how many?
- if columns - which columns? Column E, I, J, K and so on
3. What is the user NOT permitted to do when sheet protection applied? basis this validation I have keyed formula on other columns which gets auto populated those columns I need to lock and don't want users to edit, so in this case, if some inputs in column E basis this column D gets auto triggered and I don't want users to edit column D
4. Size of worksheet? Last row? Last column? Last column is AS & last row is A3001
5. Are you using any other VBA in this workbook? no, I'm not.
6. Are you protecting the workbook itself or only the worksheet? along with worksheet protecting the workbook as well, to hide formulas

this is just one worksheet of workbook, there are multiple worksheets where I'm looking for similar data validation which I haven't discussed so far, reason being, I need to get hassle free implementation in the first worksheet, then I intent to go with second level.

Thanks Yongle

 

[a1b2c3d4]

Re: how to avoid more than one Data validation from being overwrite

Dear Yongle, please assist.

 

[Yongle]

Re: how to avoid more than one Data validation from being overwrite

Will do. Away at moment. Will update thread Thursday

 

[a1b2c3d4]

Re: how to avoid more than one Data validation from being overwrite

Thanks