Stop users cutting and pasting

[Giordano Bruno]

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?

 

[zzjasonzz]

if you right click on the sheet tab and go view code then paste the following

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

 

[Giordano Bruno]

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]

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]

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]

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

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]

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.