Results 1 to 8 of 8

Thread: Avoid Copy paste over Data Validation cell

  1. #1
    Join Date
    Jun 2011
    Posts
    91

    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?

  2. #2
    Join Date
    May 2009
    Posts
    513

    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

  3. #3
    Join Date
    May 2009
    Posts
    546

    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

  4. #4
    Join Date
    May 2009
    Posts
    529

    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

  5. #5
    Join Date
    Apr 2009
    Posts
    570

    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.

  6. #6
    Join Date
    May 2009
    Posts
    542

    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

  7. #7
    Join Date
    May 2009
    Posts
    640

    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

  8. #8
    Join Date
    May 2009
    Posts
    527

    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

Similar Threads

  1. Want to paste data only in visible cell of Microsoft Excel
    By FullTimepass in forum MS Office Support
    Replies: 2
    Last Post: 16-02-2012, 06:21 PM
  2. Replies: 2
    Last Post: 16-02-2012, 06:13 PM
  3. can not copy paste data in pen drive.access is denied
    By RajKumar2020 in forum Portable Devices
    Replies: 4
    Last Post: 12-06-2011, 12:29 AM
  4. I am not able to copy and paste data in Excel 2008
    By Apple.Mac.Rocker in forum Windows Software
    Replies: 3
    Last Post: 07-10-2009, 10:27 PM
  5. Replies: 0
    Last Post: 18-03-2009, 10:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •