1

I have a worksheet where I have already prevented a user from copying and pasting a cell in a normal column into another cell in a column with data validation drop-downs.

The issue I'm now having is that a user is able to copy and paste a cell from a column with data validation into another cell in a column with data validation. Is there any way to prevent this??

Many thanks, Kieran

user2667934
  • 11
  • 1
  • 2

1 Answers1

1

You can check that the cells still have validation after a change.

Using the Workbook change event, in the ThisWorkbook module

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Does the validation range still have validation?
If Not HasValidation(Range("RangeToProtect1")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect2")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect3")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect4")) Then RestoreValidation
End Sub

Private Sub RestoreValidation()
Application.EnableEvents = False
'turn off events so this routine is not continuously fired
Application.Undo
Application.EnableEvents = True
'and turn them on again so we can catch the change next time
MsgBox "Your last operation was canceled." & _
       "It would have deleted data validation rules.", vbCritical
End Sub

Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
On Error Resume Next
Debug.Print r.Validation.Type    'don't care about result, just possible error
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

in RangeToProtect, you can either specify a specific range, or use a named range (note that a named range, while making code easy to read, can cause the code to fail if they delete the entire range that you are trying to protect)

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Only a single column can be protected using the above code. Is there any work around for this? to protect the multiple columns with data validation; where my excel sheet contains data validation in the columns A, E, S.. @SeanCheshire – MaheshVarma Sep 19 '13 at 13:36
  • 1
    A slight alteration to the code, and you can protect as many ranges as you want – SeanC Sep 19 '13 at 14:12
  • The above code works as expected for 3 columns(for example), but when a value is selected from drop down or pasted from another cell the validation message is displayed for 3 times, Even if the validation is passed. But I do not want to display the error message to the cell which has passed the validation. – MaheshVarma Sep 23 '13 at 13:24
  • Is there any way to do so? @SeanCheshire – MaheshVarma Sep 24 '13 at 05:34
  • Make sure you are *only* protecting ranges that have validation. If there are any part of the range that does not have validation, then the code will undo the change. e.g. If you have validation in `A1:A1000`, but try to protect the entire column, then it will always undo the changes, as A1001 fails the validation test – SeanC Sep 24 '13 at 12:01
  • Ya, The data validation is set to entire column so, I'm protecting the entire column, but still if a valid data is selected from the drop down the error message is displaying to the count of the columns set with the Data validation. By clicking "OK", if the condition satisfies the cell is accepting the value and if the condition fails it is not. – MaheshVarma Sep 24 '13 at 12:22
  • on the ranges, are you using a fully qualified range? e.g. if you want to protect column A, and put in the range as `A:A`, then you will get a popup if you have switched to another sheet that does not have the validation. Enter the range as `Sheet1!A:A` to avoid this issue – SeanC Sep 24 '13 at 12:29
  • I have asked a question regarding this issue here http://stackoverflow.com/questions/18977088/avoid-multiple-error-pop-up-messages-in-excel The only change which i need in your code is to avoid pop-up error message when valid data is selected from drop down/pasted into the cell. @SeanCheshire – MaheshVarma Sep 24 '13 at 12:43
  • 've been struggling for 2 days with this issue... @seancheshire – MaheshVarma Sep 24 '13 at 13:22