Limit (Not total Denial) Pasting within a Worksheet

[avsimmons]

Hello, I would like to deny pasting into certain columns (F:G) without disrupting that ability elsewhere.

I've searched for this and have come close to a solution, but they haven't been quite right.

I used the following but it restricts pasting for the entire sheet

[FONT=&quot]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT]
[FONT=&quot] Application.CutCopyMode = False[/FONT]
[FONT=&quot]End Sub

I have tried this as well. It prevent copy and pasting within the sheet, but once I copy from a separate sheet or workbook, it allows the pasting to occur.

[/FONT][FONT=&quot]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT]
[FONT=&quot] If Intersect(Target,Range("F:G")) Is Nothing Then Exit Sub[/FONT]
[FONT=&quot] Application.CutCopyMode = False
End Sub[/FONT]
[FONT=&quot]
I tried using the above with the following and the result is the same. I can prevent copy and paste from within the sheet, but it still allows copy and pasting between sheets or from an entirely separate workbook.

I obtained all of these from answers.microsoft.com and admittedly my understanding of this is basically nil.

From within Mr.Excel (courtesy of @Tom Urtis) I found the following, but the results were no more encouraging. When I applied this to my workbook, either I did it wrong (entirely probable) or something is missing, as it had no effect on copy and pasting at all:

[/FONT]Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
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 copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub[FONT=&quot]

[/FONT]Again, I don't wish to totally deny copy & paste. I want to prevent its use in certain columns within a sheet.

Thanks in advance for any help anyone can provide.

 

[DanteAmor]

The following, if you select a cell in column F or G, the memory is cleaned and you cannot paste.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("F:G")) Is Nothing Then
    On Error Resume Next
    ActiveWorkbook.Save
    Application.CutCopyMode = False
    Dim DataObj As MsForms.DataObject
    Set DataObj = New MsForms.DataObject
    DataObj.Clear
    DataObj.SetText ""
    DataObj.PutInClipboard
    DataObj.GetFromClipboard
  End If
End Sub

But if you select, say cells A4 to D4, then select cell E5 and paste, of course you can paste the data from column B4 into F5.
You have considered changing the format of the cell to locked and protecting the sheet, that way you could not write in column F, nor paste.

 

[avsimmons]

Thank you. I tried this bit of code and I keep getting an error upon testing.

Compile error:
User-defined type not defined

The Dim DataObj As MsForms.DataObject line is highlighted.

I've not used any VBA before, so I can't begin to decipher what this error is about. Hopefully you or someone else understands it.

I can't lock those columns as they are needed for entry either by typing or barcode scanning. We just want to prevent pasting due to the intent of this sheet being used as a verification document of certain activities. There is likely a simpler way to achieve the end goal, I just don't know what that is.

Thank you.

 

[DanteAmor]

For using MSForms.DataObject in your code you need library "Microsoft Forms 2.0 Object Library"
1. Open Excel workbook and press Alt + F11 to get Visual Basic Editor (VBE)
2. Go to the Tools menu and select References from the drop down menu.
3. “References- VBAProject” dialog will appear.
4. Select the check box of "Microsoft Forms 2.0 Object Library" and click OK button.

 

[avsimmons]

Awesome!! I had to find the Microsoft Forms 2.0 Object Library. It wasn't in the Reference list. Once I did that though, it worked wonderfully. It looks like it will allow you to paste, but as you said, it just loses the memory of what was on the clipboard to paste. I really appreciate the solution. THANK YOU!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.