Stop users cutting and pasting

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,298
I have an excel template with data entry areas and locked formulas.

If the user cuts and pastes in the data entry area, the formulas don't work and the conditional formatting gets messed up.

I could fix the formula problem with the OFFSET function, but not the formatting problem as the format disappears from the cut cell.

Is it possible to trap "cutting" or "pasting" with an on-event macro?

Is there another possible solution?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
if you right click on the sheet tab and go view code then paste the following

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

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,298
Thanks zzjasonzz

Is there something missing? It seems to be inactive. I've added a MsgBox to the code, but that doesn't show when I cut and paste.
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
if you pasted the code in the right place you wont need to do anything, make sure on the left of the VBA window under microsoft excel objects that you are working on the correct sheet.
 

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,298
Hi zzjasonzz.

My apologies. The problem was at my end. I refired Excel and it works. The problem is that it also stops copying. I would like users to be able to copy, but not to cut. Is that possible?
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Mmmm, you probably have to put code on the workbook open macro to remove the cut option from the menu and use application.onkey to disable CTRL-X from working as well..

Bit of a hassle and also can cause problems if people leave the sheet open and do other things etc

something like

Code:
Private Sub Workbook_Open()
   ' Disable the shortcut key for cut
   Application.OnKey "^x", ""
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
      ' enable the shortcut key
   Application.OnKey "^x"           ' leave out second parameter to reset
End Sub

for a good example look here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
 

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,298
Many thanks zzjasonzz.

That's very helpful. Also the link is particularly useful.

As you remark it's a bit of a hassle. My alternative strategy is to have a change event macro fix up the mess after the damage has been done. My first thoughts are that that will be a simpler and better solution.

Thanks again for the very useful help and for your interest.
 

Forum statistics

Threads
1,181,054
Messages
5,927,855
Members
436,573
Latest member
CMR237

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