How to clear the clipboard when certain cells are selected? (VBA)

[Rnkhch]

Hello,

I have some cells that I would prefer my students to type into them rather than just quickly copy/paste. I was wondering if a code could be written for this purpose?

In particular, I want to apply this to the range M4:O4 of my sheet to force my students to think and manually type in the required contents. So if they have copied something either in Excel or outside, I want the clipboard to immediately clear upon clicking on any of the cells in M4:O4.

Thanks much for any input!

 

[JoeMo]

1. Put this code in a standard module:

Option Explicit
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

Public Function ClearClipboard()
  OpenClipboard (0&)
  EmptyClipboard
  CloseClipboard
End Function

Sub ClipboardClear()
  Call ClearClipboard
End Sub

2. Put this code in the worksheet that has the student input range you want to restrict from pasting:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("M4:O4")) Is Nothing Then
    Call ClipboardClear
    MsgBox "Cells M4:O4 require manual input, no pasting permitted"
    Exit Sub
End If
End Sub

The selection_change event macro will trigger automatically and clear the clipboard whenever one or more cells in the restricted range is selected.

 

[Rnkhch]

Thank you! I got this error:

"The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them the PtrSafe attribute."

Any tweaks can be done?

 

[JoeMo]

Replace the standard module with this:

Option Explicit
#If VBA7 Then
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
#End If

Public Function ClearClipboard()
  OpenClipboard (0&)
  EmptyClipboard
  CloseClipboard
End Function

Sub ClipboardClear()
  Call ClearClipboard
End Sub

 

[Rnkhch]

Thank you! Now it works very well, but I encountered a small, unexpected issue that I hope you can fix for me:

I'm using the code from the solution post of my other thread (How to temporarily unprotect a sheet to allow VBA code to run and reprotect immediately, using VBA?) in the same sheet, and that code also contains a sub with the name "Worksheet_SelectionChange". So I get the error "Ambiguous name detected".

I tried changing the sub name in your code, but it didn't work.

Thank you so much!

 

[johnnyL]

@Rnkhch, add the @JoeMo code for the worksheet_selection section

If Not Intersect(Target, Range("M4:O4")) Is Nothing Then
    Call ClipboardClear
    MsgBox "Cells M4:O4 require manual input, no pasting permitted"
    Exit Sub
End If

to the top of your existing worksheet_selection section In other words, combine the two sections to one section.

 

[Rnkhch]

Yay! Thanks! It's working like a charm Another piece of the puzzle solved

Now which post should I mark as solution?

 

[johnnyL]

Not mine

 

[Rnkhch]

Cool, I marked post #4 as solution.

I think together with the other two threads we worked on, these must have been the most versatile/advanced 'copy/pasting' solution to date I'm super excited

 

[JoeMo]

Glad I could help and happy to hear you've found lots of help from others here too.