Disable Cut/Copy/Paste functions in one worksheet in a workbook 1

[Annlala]

Hi!

I'm having difficulty with a string of code. What I am trying to accomplish is to turn off the cut,copy and paste functions on one worksheet in a workbook. This includes the command bars, right click options and hot keys. The problem is that what I have so far (see below) works for the sheet in question--but when I go to open another workbook--the code still seems to be in effect. Could this be a fluke/program bug? I want to make sure I can remove this oddity--as this sheet goes out to about 70 staff and I will receive endless complaints if they can't see the Edit menu! Please review (admittedly, I have no formal training--everything I know is from trial and error!)

I'm grateful for any help I can get!!! Simplification--or other suggestions!

'******For Cut/Copy/Paste removal--in ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   If (Sh.Name = "2007") Then
 'Disable Edit menu
Application.CommandBars.FindControl(ID:=30003).Enabled = False
 'Disable Copy
Application.CommandBars("Standard").FindControl(ID:=19).Enabled = False
'Disable Cut 
Application.CommandBars("Standard").FindControl(ID:=21).Enabled = False
'Disable Paste
 Application.CommandBars("Standard").FindControl(ID:=22).Enabled = False
 Application.CellDragAndDrop = False
                
'Disable Ctrl-c (Copy)

Application.OnKey "^c", ""

'Disable Ctrl-x (Cut)

Application.OnKey "^x", ""

'Disable Ctrl-v (Paste)

Application.OnKey "^v", ""

Else
Application.CommandBars.FindControl(ID:=30003).Enabled = True
 Application.CommandBars("Standard").FindControl(ID:=19).Enabled = True
 Application.CommandBars("Standard").FindControl(ID:=21).Enabled = True
 Application.CommandBars("Standard").FindControl(ID:=22).Enabled = True
 Application.CellDragAndDrop = True
                
'Enable Ctrl-c (Copy)

Application.OnKey "^c"

'Enable Ctrl-x (Cut)

Application.OnKey "^x"

'Enable Ctrl-v (Paste)

Application.OnKey "^v"

End If
End Sub

**I removed the right click issues in the 2007 worksheet itself w/ the following:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

 

[CBasicAsslember]

Why don't you tell us why you need to disable everything?

 

[Annlala]

Can do!

The situation is that I prepare an expense voucher for our staff members to fill out and submit to the office. It is then audited and imported into the accounting system using one of the other worksheets in the workbook.

I would like to shut down the cut/copy/paste functionality for two reasons:

1) The staff tend to copy/cut and paste information from different expense categories, which then return #REF! errors. I have tried to reconfigure the Excel formats to avoid this. I have tried the following (A2 is the cell they would be moving) 1) $A$2+(A1*.45); 2) $A2+(A1*.45); and 3) A2+(A1*.45). I've also tried to do an INDIRECT on the cell in question. I then encountered formatting errors--i.e. if they copied a value in an unmerged cell to a merged cell, it would unmerge, etc.

2) I have data validations present in the great majority of cells to, essentially, fill out all the combo-boxes and surrounding cells with correct values,etc. This is an ongoing problem and I have found data validation works great--until they copy/cut and paste another value into the cell. Without any behind the scenes coding, this means bye-bye formatting and data validation (right?).

If there is anyway to avoid situations 1 & 2 without locking down the editing capabilities (staff won't be thrilled with that either...), I'm more than game!

**P.S. my "technical user" is more like "Accountant that got an interesting project dropped in her lap". So, please pardon my programming weaknesses. I'll try anything to get it to work.

 

[TonyJollans]

I'm not sure that disabling copy and paste is necessarily the answer but what you have looks reasonably comprehensive. It is, as you say, globally effective and if you only want it in one worksheet you must switch it off on sheet deactivate and re-enable (re-disable?) it on return. If memory serves there are some situations where deactivate/activate don't quite fire as you might expect when switching in and out of Excel but I can't recall the details.



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[CBasicAsslember]

In the VB editor paste this code in "This Workbook".

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' Change 'Sheet1' to the name of the sheet that you want to protect
If ActiveSheet.Name = "Sheet1" Then
    
    With Application
        .CellDragAndDrop = False
        .CutCopyMode = False
     End With
End If
End Sub

 

[Annlala]

Tony & CBasic,

Thanks for your help! Let me try the code and I'll get back to you. I really appreciate it.

Regards,

Angie

 

[CBasicAsslember]

Warning. If you disable drag and drop it will disable it for the application. You will need to re-enable when the user closes the workbook or it will not work in any workbook.

 

[Annlala]

Okay, I gave it a try. The result I get (I made a copy of the workbook and took out all other code) is all sheets with the Edit menu grayed out, a right click menu that's still present and live hot keys. Do I still need to include my code for those items with the new one?

As well, when I go to open new workbooks, I am still unable to use the Edit menu. When I got to the code in any given workbook, there is something under what is the "Sheet 1" (which does not have the same name as the "Sheet 1" that I put into the code above), I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Should that still be showing? As well, it doesn't seem as though it is working on a sheet-specific level--the other worksheets in the workbook are also without the edit menu, but the other copy/cut/paste tools are present. Thanks for all the suggestions! I'll keep playing around too, maybe I'll strike gold!

 

[CBasicAsslember]

For the cut/copy paste it doesn't matter if the menu's are active or not. As soon as they change cells the code will clear the clipboard effectively killing the paste operation. In fact, I would advise against disabling menus as it will do it for all the user workbooks. Remove the code I posted previously. For disabling cut/copy for a sheet use this in the sheet module:

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

This will cancel any attempt to cut or copy on the sheet.

Drag and drop is a bit more problematic since it changes the application. IOW, it will carry over to other workbooks if it isn't changed back. The same is true of the menus.