Preventing Data Validation from being deleted

[underdunk]

I have this code that does not allow users to paste over cells with data validation

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("J10:J1999")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Please enter a valid date.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

It works great until I protect the sheet and have these cells unlocked, it allows any value to be pasted into the cells.

 

[Mister H]

Hi underdunk:

Not sure what your file is used for but if it is just a template type of file you could set your original file as Read Only and then the user can not save over the original file. Maybe this would not work in your situation but thought I would throw it out there anyway. If you need any clarification just let me know.

Bye 4 Now,
Mark

 

[Gingertrees]

Best thing would be to disable cut/paste all together. This is some of my favorite code, that I think I got from mdmackillop and/or GTO over at VBA Express. It goes in the ThisWorkbook module.
NOTE: works best with some additional code to force user to enable macros (otherwise they can copy/paste to their hearts' content):

Option Explicit
Dim bolMyOverride As Boolean
'this is your backdoor in, so YOU can run EnableStuff... and do copy/paste,
'but your users cannot.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
        Call HideSheets
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
End Sub
Private Sub Workbook_Activate()
     
     '// After you have run 'EnableStuffSoICanWork()', then the Boolean 'bolMyOverride' //
     '// equals TRUE. //
     
     '// So... assuming you've run the aforementioned sub and bolMyOverride has been set //
     '// to True, the below test fails, and 'CutCopy_Disable' is never called. In short,//
     '// as long as bolMyOverride retains a value of True, you can make mods w/o //
     '// interference, as long as you don't reset. //
    If Not bolMyOverride Then
         '// Code moved to own sub //
        Call CutCopy_Disable
    End If
End Sub
 
Private Sub Workbook_Deactivate()
     
     '// SAA //
    If Not bolMyOverride Then
        Call CutCopy_Enable
    End If
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     
    If Not bolMyOverride Then
        With Application
            .CellDragAndDrop = False
            .CutCopyMode = False 'Clear clipboard
        End With
    End If
End Sub

Private Sub EnableStuffSoICanWork()
    Call CutCopy_Enable
    bolMyOverride = True
End Sub
 
Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
    Call CutCopy_Disable
    bolMyOverride = False
     '// Optional of course //
    ThisWorkbook.Save
End Sub
 
Private Sub CutCopy_Disable()
    Dim oCtrl As Office.CommandBarControl
     
     'Disable all Cut menus
    For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
        oCtrl.Enabled = False
    Next oCtrl
     
     'Disable all Copy menus
    For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
        oCtrl.Enabled = False
    Next oCtrl
     
    Application.CellDragAndDrop = False
End Sub
 
Private Sub CutCopy_Enable()
    Dim oCtrl As Office.CommandBarControl
     
     'Enable all Cut menus
    For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
        oCtrl.Enabled = True
    Next oCtrl
     
     'Enable all Copy menus
    For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
        oCtrl.Enabled = True
    Next oCtrl
     
    Application.CellDragAndDrop = True
End Sub

 

[underdunk]

Thanks for the suggestions, but I think disabling copying and pasting is a bit extreme. I just don't want them to do it in the specified cells.

 

[Gingertrees]

That's what I thought at first - but it saves you headaches in the long run. Remember that the simple fact that you can write code puts you ahead of the vast majority of office drones. You would NEVER think to ignore a Read Only error, or type a phone number using back-slashes, or enter a date with a 3-digit year...but there are lots of folks who will. Some of those folks will use your workbook.
Cheers,
~G