Avoid Copy paste over Data Validation cell
For some of my cells I have drop-down menus. It is such that the user can select value from the drop-down list but when a user types anything that is not in the list an error message pops up via data validation error alert. I works fine when the user copy paste a cell with different drop list into a cell by overwriting the existing validation. I cannot totally lock the cells. What to do next?
Re: Avoid Copy paste over Data Validation cell
Your issue seems to be an interesting one. Try the below VBA code. If you see the syntax in that, its very new and tricky but it will do the job for you.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If HasValidation(Range(ActiveCell.Address)) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
Re: Avoid Copy paste over Data Validation cell
The below VBA is older one but its very easy and convenient to use. You can easily understand after looking at the code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect 'only if your sheet is protected
If Selection.SpecialCells(xlCellTypeAllValidation).Count <> Selection.Cells.Count Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox ("THE OPERATION YOU JUST PERFORMED WOULD HAVE ERASED DATA VAIDATION, THEREFORE IT WAS CANCELLED")
ActiveSheet.Protect 'only if you want protect your sheet
Else
End If
End Sub
Re: Avoid Copy paste over Data Validation cell
Just keep in mind that the below code should be utilized in the indication where there is data validation, its important. These will avoid error message due to data validation. The data validation is in the Columns that are mentioned in the code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 1,2,3
ActiveSheet.Unprotect 'only if your sheet is protected
If Selection.SpecialCells(xlCellTypeAllValidation).Count <> Selection.Cells.Count Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox ("THE OPERATION YOU JUST PERFORMED WOULD HAVE ERASED DATA VAIDATION, THEREFORE IT WAS CANCELLED")
Else
End If
ActiveSheet.Protect 'only if you want protect your sheet
End Select
End Sub
Re: Avoid Copy paste over Data Validation cell
I am very much familiar to such kind of issues because I have personally faced similar one. In Office/Excel 2010 the default code prevents us to paste data into the cells that has data validation. You must also be getting stop error multiple times, to stop the message appearing you have to continuous press Escape key. Not only that, even double click on a cell causes the same stop message.
Re: Avoid Copy paste over Data Validation cell
The following VBA uses an event procedure. You can have look at the code and make necessary changes in it as per your needs then use it.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", 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
Re: Avoid Copy paste over Data Validation cell
You can target the cells directly to disallow copy, paste, drag & drop changes in validation cells. Below it the procedure to do that:
Code:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
With Application
.CutCopyMode = False
.CellDragAndDrop = False
.OnKey "^c", ""
End With
End Sub
Private Sub Workbook_Activate()
With Application
.CutCopyMode = False
.CellDragAndDrop = False
.OnKey "^c", ""
End With
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot cut, copy, paste, or ''drag & drop''.", 16, "For this file:"
End Sub
Re: Avoid Copy paste over Data Validation cell
You can also do that by using a different unique technique such as disallowing the keys of copy, cut and paste. The VBA for that is:
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application
.OnKey "^c", ""
.CutCopyMode = False
.CellDragAndDrop = False
End With
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub
Private Sub Workbook_Deactivate()
With Application
.CellDragAndDrop = True
.OnKey "^c"
.CutCopyMode = False
End With
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
With Application
.CellDragAndDrop = True
.OnKey "^c"
.CutCopyMode = False
End With
End Sub