Disable cut/copy/paste only on certain ranges?

Why don't you just lock the cells in question?

Click to expand...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("e7")) Is Nothing Then Exit Sub
    Range("d7").Copy
    Application.EnableEvents = False
    On Error GoTo Catch1
    Target.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    If Not Target.Validation.Value Then
        MsgBox "Oops! That's not a valid value." & vbNewLine _
            & "Please select from the drop down list"
        Target.ClearContents
        Target.Activate
        End If
    GoTo Finally1
Catch1:
    Resume Finally1
Finally1:
    Application.EnableEvents = True
    End Sub

The below code disables cut/copy/paste in a worksheet

Code:

Private Sub worksheet_activate()
  EnableControl 21, False   ' cut
  EnableControl 19, False   ' copy
  EnableControl 22, False   ' paste
  EnableControl 755, False  ' pastespecial
  Application.OnKey "^c", ""
  Application.OnKey "^v", ""
  Application.OnKey "+{DEL}", ""
  Application.OnKey "+{INSERT}", ""
  Application.CellDragAndDrop = False
End Sub

Private Sub worksheet_deactivate()
  EnableControl 21, True   ' cut
  EnableControl 19, True   ' copy
  EnableControl 22, True   ' paste
  EnableControl 755, True  ' pastespecial
  Application.OnKey "^c"
  Application.OnKey "^v"
  Application.OnKey "+{DEL}"
  Application.OnKey "+{INSERT}"
  Application.CellDragAndDrop = True
End Sub

Is there a way to make it only work when a certain range is selected, e.g. range(a1:a10")? In short users should be able to cut copy and paste except only on a1:a10?

Click to expand...

I saw this post earlier and was hoping that somebody would come up with something better than what I was determining. Maybe there isn't an easy way to accomplish what you are looking for? Even if we were to utilize code to protect your range, we would only be able to react after the fact. For example, if we were to run the code located within your worksheet_activate event when some cell or cells were selected in the range a1:a10, it would be after a paste or drag operation. Not knowing the details of what validation or level of protection you are using in range a1:a10, it is pretty much impossible to give you the most basic example. I think you should start over by defining for potential responders what constraints you wish to place on this range. There may be a better method available or a more logical approach than disabling copying, pasting, dragging, and dropping. I have used mirroring in times past to return a range to it's previous values if validation failed. This is, in effect, creating a mirror copy of some range on a hidden worksheet. If the user makes invalid changes, this copy is used to restore the range to it's previous values, formatting, ect... However, this may be overkill and that is why I have hesitated to post an example. Please provide some more details about your range and any other relevant details about your project...

Click to expand...

{snip}
That is why my only option is to disable pasting when a range in the sheet is selected.

Regards

Click to expand...

{snip}
That is why my only option is to disable pasting when a range in the sheet is selected.

Regards

Click to expand...

No, it isn't. I gave you a *working* solution that provides you with the data integrity you want and your customers with the flexibility of using the UI to its fullest capability.

Click to expand...