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

Dear Yongle,
Don't know, but there is some disconnect. This code is working at times and not working at times. At times it is allowing me to overwrite validation areas and at times not, at times it is not allowing me to copy paste data at non-validation areas as well. It is undoing the action at non-validation areas. Please assist. I started facing these issues after I tried protecting worksheet & workbook, since then this code is not working properly thought I had unprotected the sheets and workbooks and closed down the excel and re-open it.

 

[Yongle]

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

1. Please use code tags and post the relevant code you are currently using...
Code in ThisWorkbook module
Code in Standard module?
Code in Sheet module?

2. Is the sheet protected with a password? (I do not need the password)

 

[a1b2c3d4]

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

Dear Yongle,
Please find below:

Code in ThisWorkbook module
Private Sub Workbook_Open() OldDV = Sheets("Active").Cells.SpecialCells(xlCellTypeAllValidation).Address
ChangeDV = False
End Sub


Code in Standard module?
Public NewDV As String
Public OldDV As String
Public ChangeDV As Boolean

Code in Sheet module?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UndoAction As String
UndoAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoAction, 5) = "Paste" Or UndoAction = "Auto Fill" Then
If ChangeDV = False Then
Application.EnableEvents = False
On Error Resume Next
NewDV = Cells.SpecialCells(xlCellTypeAllValidation).Address
If Not NewDV = OldDV Then Application.Undo
Application.EnableEvents = True
Else
ChangeDV = False
OldDV = Cells.SpecialCells(xlCellTypeAllValidation).Address
End If
End If
End Sub

2. Is the sheet protected with a password? (I do not need the password)
I tried doing both, using password & without password, initially the problem started when I made it password protected, where some error got populated on screen stating this code cannot be run with sheet protected.

 

[Yongle]

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

Thanks - will look at this later today

 

[a1b2c3d4]

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

ok, thanks !

 

[Yongle]

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

Try this...- (amend the value to match your password)

When workbook is opened,
- sheet is unprotected by VBA
- protection removed from all DV cells
- sheet protection is re-applied
- pWord declared as a public variable so that it can be used elsewhere
(VBA remembers the password whilst the workbook remains open)

in this workbook module

Private Sub Workbook_Open()
    pWord = "[COLOR=#b22222]password[/COLOR]"
    With Sheets("Active")
        .Unprotect (pWord)
        OldDV = .Cells.SpecialCells(xlCellTypeAllValidation).Address
        .Range(OldDV).Locked = False
        .Protect (pWord)
    End With
    ChangeDV = False
End Sub

in standard module

Public NewDV As String
Public OldDV As String
Public ChangeDV As Boolean
Public pWord As String

in sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UndoAction As String
    UndoAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoAction, 5) = "Paste" Or UndoAction = "Auto Fill" Then
        If ChangeDV = False Then
            Application.EnableEvents = False
            On Error Resume Next
            NewDV = Cells.SpecialCells(xlCellTypeAllValidation).Address
            If Not NewDV = OldDV Then
                Application.Undo
            End If
            Application.EnableEvents = True
        Else
            ChangeDV = False
            OldDV = Cells.SpecialCells(xlCellTypeAllValidation).Address
        End If
    End If
End Sub

 

[a1b2c3d4]

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

Thanks Yongle, will check & confirm you if all is working fine.

 

[a1b2c3d4]

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

Friend, below problem still persists.
a) the column where data validation is used, and if I try to copy paste the data of same values, it is doing undo to all my copy paste options or copy paste special values options, irrespective of the reasons whether the values are of in lines to validation or not. Only manual typing is accepted.
b) the column where there is no data validation, same problem persists, it is not allowing me to use copy paste or paste special values, it is only allowing me to manually type.
In a nut shell, copy paste options or paste special values are non acceptable in the entire sheet.

 

[a1b2c3d4]

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

Any help on my last query.

 

[Yongle]

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

Let's begin again. This time let's understand the complete picture from the beginning. There may be a simpler approach.

1. Which version of Excel are you using?
2. Are your data validation cells individual cells or whole columns?
- if individual cells - how many?
- if columns - which columns?
3. What is the user NOT permitted to do when sheet protection applied?
4. Size of worksheet? Last row? Last column?
5. Are you using any other VBA in this workbook?
6. Are you protecting the workbook itself or only the worksheet?

thanks