Data validation - paste

[jynxy]

Hi,

i have lots of dropdowns in different columns, i.e E6:E405 J6:J405 etc, the dropdown is using an if to check if holidays are allowed, depending if this is yes or no it will show one of two lists, one with holidays,lieu,unpaid and one without just showing things like shift swap etc.

because of this i need to prevent cells being overridden unless the value is in the list being used, this is because the holiday availability is now used and needs to be locked down, but i need to be able to enter the other options still, is there anyway to prevent the cells being overwritten ? ideally still being able to copy and paste dropdown values only.

Thanks

 

[jynxy]

I have found this code below, this has mainly worked for what i need, i have few issues i can see so far,

a big issue i have with this, is if I'm using an if to display two different lists, these are both similar except the shorter one does not have holidays, lieu etc listed, with the below code if i copy and paste, it works as expected by not allowing unless in the list, now the problem is if the data is in the longer list and copied over a dropdown with the shorter list, it will still allow this.

second issues is Data validation / dropdown should not be deleted and remain as it was. i also need to allow blank if no option is to be selected. i can select blank which is fine, if i press delete it removes the dropdown, is this possible to make this blank only and leave the dropdown.

lastly i have around 31 columns of dropdowns so E2:E405 H2:H405 M2:M405 etc is there anyway to make this work for all of them without duplicating code ?

For example

Long List	Short List
Holiday	Shift Swap Out
Lieu	Shift Swap In
Shift Swap Out	Bereavement
Shift Swap in	Absence
Bereavement
Absence

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Does the validation range still have validation?
    If Not Intersect(Range("E:E"), Target) Is Nothing Then
        Application.EnableEvents = False
        If HasValidation(Intersect(Range("E:E"), Target)) = False Then
            Application.Undo
            MsgBox "Your last operation was canceled." & vbCrLf & _
                "It would have deleted data validation rules.", vbCritical
        End If
    End If
    Application.EnableEvents = True
End Sub

Private Function HasValidation(r As Range) As Boolean
    ' Returns True if every cell in Range r uses Data Validation
    Dim c As Range
    Dim x As XlDVType
    On Error Resume Next
    For Each c In r.Cells
        x = r.Validation.Type
        If Err Then
            HasValidation = False
            Exit Function
        End If
    Next c
    HasValidation = True
End Function