Disabling the use of Cut in a specific workbook only

 Hi,

Over the years I have encountered problems whereby my users have persisted in using the Cut (and then paste) operation in password protected templates. Whenever a user Cuts and then pastes data within the template, it causes #REF errors to appear.

At the start, I took the Mr. NiceGuy approach, and added in a message when the workbook was opened politely asking them NOT to use Cut and Paste (copy is ok) in the template.

Unfortunately, despite this, some people still continue to use it.

So, what I would like to do is to disable the Cut command - but only in specific workbooks [so, if they open another workbook, Cut would work in that but NOT in teh specified workbook].

What woudl be teh most effective way of achieving this?

 

 

Try this

Option Explicit

Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    Call TestWorkbook(WBName:=Wb.Name)
End Sub

Private Sub Workbook_Open()
   Set App = Application
   Call TestWorkbook(WBName:=ActiveWorkbook.Name)
End Sub

Private Function TestWorkbook(ByVal WBName As String)
Dim fTarget As Boolean

    fTarget = WBName = "Stats 2008.xls"

    With Application
    
        .CommandBars(1).Controls("Edit").Controls("Cut").Enabled = Not fTarget
        .CommandBars("Cell").Controls("Cut").Enabled = Not fTarget
        
        If fTarget Then
        
            .OnKey "^{X}", ""
        Else
        
            .OnKey "^{X}"
        End If
    End With

End Function

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

 Bob,

Thank you for this.

Sorry to ask another question, but this works for the command bar (which is great), but what woudl I need to include to prevent the use of Ctrl X ?

 

I did try, but I had some typos

 

Option Explicit

Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    Call TestWorkbook(WBName:=Wb.Name)
End Sub

Private Sub Workbook_Open()
   Set App = Application
   Call TestWorkbook(WBName:=ActiveWorkbook.Name)
End Sub

Private Function TestWorkbook(ByVal WBName As String)
Dim fTarget As Boolean

    fTarget = WBName = "Stats 2008.xls"

    With Application
    
        .CommandBars(1).Controls("Edit").Controls("Cut").Enabled = Not fTarget
        .CommandBars("Cell").Controls("Cut").Enabled = Not fTarget
        
        If fTarget Then
        
            .OnKey "^x", ""
        Else
        
            .OnKey "^x"
        End If
    End With

End Function

 Bob,

I added a piece of code as shown below, and it disables the Menu command as well as the keyboard shortcut. Many thanks for taking the time and effort to help - i appreciate it enormously.

Private Function TestWorkbook(ByVal WBName As String)
Dim fTarget As Boolean
fTarget = WBName = "Test_prevent_Cut.xls"
With Application
.CommandBars(1).Controls("Edit").Controls("Cut").Enabled = Not fTarget
.CommandBars("Cell").Controls("Cut").Enabled = Not fTarget
If fTarget Then
.OnKey "^{X}", ""
Else
.OnKey "^{X}"
End If
End With

If fTarget Then
Application.OnKey "^{x}", ""
Else
Application.OnKey "^{x}"
End If

End Function

 

 Hi,

 

If you really wanna get fancy, check out this post on my site:

http://www.jkp-ads.com/articles/catchpaste.asp

Alan,

I can't see what you have added, but you seem to have my original code, not my amended code.

 Bob

I added a second If statement - this time using a lower case x.

I went back and tried the first code you sent using a lower case x, and it works!

Sorry for the confusion (and my bad eyesight !)

My second post in this thread used a lower-case x, and got rid of the curly brackets. It is the lower-case x that is the key, otherwise it is equivalent to ctrl-shift-x, something quite different.