MsgBox when user attempts to copy/paste

Lrthornt

New Member
Joined
Mar 28, 2017
Messages
3
Hello and thanks for your time.

[I am using Microsoft Office 2013]

I have prevented users from copying/pasting within a worksheet with the following VBA code (found online):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End Sub

I would now like to create a MsgBox when users attempt to copy/paste informing them that this function has been disabled.
Is there a simple line of code to do this?

I have found other (more complicated solutions) to do this but have found that they do not work as successfully and wish to keep the simple code above.

Any help would be much appreciated
LRT
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Lrthornt

New Member
Joined
Mar 28, 2017
Messages
3
P.S > I have tried the following code several times but cant get it to work (copy and paste still work!)...




'*** In a standard module ***

Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***

Option Explicit
Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
Lrthornt,

Welcome to the Board.

Another approach would be to create a Workbook_Open and/or a Worksheet_Activate event...

Code:
Private Sub Workbook_Open()
    MsgBox "Be advised... Copy/Paste is disabled on ***worksheet name***."
End Sub

Code:
Private Sub Worksheet_Activate()
    MsgBox "Be advised... Copy/Paste is disabled on this worksheet."
End Sub

Cheers,

tonyyy
 

Lrthornt

New Member
Joined
Mar 28, 2017
Messages
3
Many thanks for your response Tonyyy.
I eventually found a solution using Application.onkey (below)...



*** In the ThisWorkbook Module ***

Option Explicit

Private Sub Workbook_Activate()
Application.OnKey "^v", "NoPaste"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^v"
End Sub




*** In a standard module ***

Sub NoPaste()
MsgBox "Sorry, Copy & Paste has been deactivated"
End Sub

:)

 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
You're welcome. Glad you found something that works...
 

Forum statistics

Threads
1,182,145
Messages
5,933,889
Members
436,916
Latest member
LonN90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top