VBA - Disable cut, copy, and drag/drop

[sje423]

I am trying to prevent users from using cut, copy, or drag/ drop in a certain file. I used the following code:

Private Sub Workbook_DeActivate()
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End Sub

This seems to give me the desired result, EXCEPT that once I close this file the applications are still deactivated. I need to disable the applications only in this file.

As a side effect, I presently cannot use cut & copy in other files. I can use drag & drop (but only after I manually select that option when going to tools, options, edit.

Any ideas?
Thanks

 

[Mudface]

Set the properties back to True in the file's BeforeClose event.

 

[Andrew Poulsom]

You could try using Application level events:

' ***********************
' Class module named Class1
' ***********************

Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
'   *** Change name of workbook to suit ***
    If UCase(Wb.Name) = "BOOK1.XLS" Then
        Application.CutCopyMode = False
        Application.CellDragAndDrop = False
    End If
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
'   *** Change name of workbook to suit ***
    If UCase(Wb.Name) = "BOOK1.XLS" Then
        Application.CutCopyMode = True
        Application.CellDragAndDrop = True
    End If
End Sub

' ******************
' ThisWorkbook module
' ******************

Private Sub Workbook_Open()
    Call InitializeApp
End Sub

' *************
' General module
' *************

Dim X As New Class1

Sub InitializeApp()
'   Called by Workbook_Open
    Set X.App = Application
End Sub

 

[Iridium]

Some of this thread may be relevant

http://www.mrexcel.com/board2/viewtopic.php?p=206642#206642

HTH

 

[sje423]

It Still Does Not Work

I now have the following code and it still doesn't work.

Private Sub Workbook_DeActivate()
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CutCopyMode = True
Application.CellDragAndDrop = True
End Sub

 

[Mudface]

See Andrew's code. I posted without thinking, sorry.

 

[sje423]

Still not giving me what I need

I set up file AAA.XLS to try out Andrew's code.

While AAA.XLS is open, I have what I need as far as not being able to use
cut, copy, etc....but when I close AAA.XLS and open another file I still cannot cut, copy, etc...

Here is what I have in each area.


This Workbook

Private Sub Workbook_Open()
InitializeApp
End Sub


Module1

Dim X As New Class1

Sub InitializeApp()
Set X.App = Application
End Sub


Class1

Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
If UCase(Wb.Name) = "AAA.XLS" Then
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End If
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
If UCase(Wb.Name) = "AAA.XLS" Then
Application.CutCopyMode = True
Application.CellDragAndDrop = True
End If
End Sub

 

[Andrew Poulsom]

The WorkbookDeactivate fires when you close a workbook, so that should be OK.

I am not sure about having what you need. Application.CutCopyMode = False clears the clipboard. With my code I can happily Cut, Copy and Paste within any workbook, but I cannot paste into a workbook something that I copied in another workbook. That's because resetting the CellDragAndDrop property also clears the clipboard (as does doing anything else between the Copy and Paste operations).

 

[CA_User]

I don't usually add a question to an existing thread by my quesiton is 100% related to Andrew's comment above from some time ago "...but I cannot paste into a workbook something that I copied in another workbook. That's because resetting the CellDragAndDrop property also clears the clipboard " ...

I want to enable the user to copy from another file and paste into current workbook. My drag and drop is disabled. Is there a work around to allow copy and paste from another workbook?

 

[Jaafar Tribak]

I don't usually add a question to an existing thread by my quesiton is 100% related to Andrew's comment above from some time ago "...but I cannot paste into a workbook something that I copied in another workbook. That's because resetting the CellDragAndDrop property also clears the clipboard " ...

I want to enable the user to copy from another file and paste into current workbook. My drag and drop is disabled. Is there a work around to allow copy and paste from another workbook?

You could try temporarly locking the clipboard right before resetting the CellDragAndDrop property .. take a look at post 9 here