Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Avoid Copy paste over Data Validation cell

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 13-07-2011
Member
 
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?

Reply With Quote
  #2  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 501
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
Reply With Quote
  #3  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 532
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
Reply With Quote
  #4  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 522
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
Reply With Quote
  #5  
Old 13-07-2011
Member
 
Join Date: Apr 2009
Posts: 566
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.
Reply With Quote
  #6  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 531
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
Reply With Quote
  #7  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 614
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
Reply With Quote
  #8  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 523
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Avoid Copy paste over Data Validation cell"
Thread Thread Starter Forum Replies Last Post
Want to paste data only in visible cell of Microsoft Excel FullTimepass MS Office Support 2 16-02-2012 06:21 PM
How to ignore blank cell in a range while using Data Validation Nicoloid MS Office Support 2 16-02-2012 06:13 PM
can not copy paste data in pen drive.access is denied RajKumar2020 Portable Devices 4 12-06-2011 12:29 AM
I am not able to copy and paste data in Excel 2008 Apple.Mac.Rocker Windows Software 3 07-10-2009 10:27 PM
Copy Text from One Cell to Another in Microsoft Excel without copy & paste Computer_Freak Tips & Tweaks 0 18-03-2009 10:00 PM


All times are GMT +5.5. The time now is 11:48 PM.