VBA - Disable cut, copy, and drag/drop

S

sje423

New Member
Joined
Jul 29, 2002
Messages
13
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
You could try using Application level events:

Code: 
' ***********************
' 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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
CA_User said:
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?
Click to expand...

You could try temporarly locking the clipboard right before resetting the CellDragAndDrop property .. take a look at post 9 here
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Re-enabling Copy, Cut, Paste, Drag/Drop Cells in Excel
Replies
5
Views
733
Peter_SSs
Peter_SSs
H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
78
HeinrichPaul
H
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
F
  • Question
Filtering/Copying a table via VBA.
Replies
2
Views
112
frobeniusabnorm
F
B
  • Solved
Run macro when drop-down value changes
Replies
3
Views
792
Joe4
Joe4

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top