Disable paste but allow pastespecial values

Iridium

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Found this code somewhere on the web a while ago

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim VRange As Range
    Set VRange = Range("Data_Entry_Cells")
    
    For Each Cell In Target
        If Union(Cell, VRange).Address = VRange.Address Then
            Application.CutCopyMode = False
            
        End If
    Next Cell
End Sub

What I want to do is modify it so that pasting is disabled but allowing pastespecial of values.

Any ideas?

TIA
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Just to finish off

Ok -I've had a look at the link and I'm most of the way there now. What I've done is put the DisableCopyCutAndPaste macro in the workbook_open, the EnableCopyCutAndPaste macro in the workbook_beforeclose and the remaining as Public Subs in a standard module. Now I'm a bit stuck as my VBA isn' too hot.

  1. I get a runtime error 91 (?) with this line of code
    Code: 
    CommandBars("ToolBar List").Enabled = True/False
  2. What modifications do I need to make so that cutting and pasting is disabled but copying and pastespecial of values is permitted

Once again TIA
 
Upvote 0
Here's another possibility (not thoroughly tested) :-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Set VRange = Range("Data_Entry_Cells")
For Each cell In Target
If Union(cell, VRange).Address <> VRange.Address Then Exit Sub
If GetFromClipboard = "" Then Exit Sub
If MsgBox("Do you want to paste the values from the clipboard?", vbOKCancel) = vbCancel Then
Exit Sub
Else
cell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If
Next
End Sub

Function GetFromClipboard() As Variant
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard
On Error Resume Next
GetFromClipboard = MyDataObj.GetText()
On Error GoTo 0
End Function
 
Upvote 0
Re: Just to finish off

Chris Chattin said:
Ok -I've had a look at the link and I'm most of the way there now. What I've done is put the DisableCopyCutAndPaste macro in the workbook_open, the EnableCopyCutAndPaste macro in the workbook_beforeclose and the remaining as Public Subs in a standard module. Now I'm a bit stuck as my VBA isn' too hot.

  1. I get a runtime error 91 (?) with this line of code
    Code: 
    CommandBars("ToolBar List").Enabled = True/False
  2. What modifications do I need to make so that cutting and pasting is disabled but copying and pastespecial of values is permitted

Once again TIA
Click to expand...

CommandBars("ToolBar List").Enabled = False worked for me in both Excel 97 and Excel 2000. It disables the list you get when you right click a Toolbar.

I think you just need the following lines from Ivan's code:

EnableControl 21, False ' cut
EnableControl 22, False ' paste
Application.OnKey "^v", "Dummy"
Application.OnKey "+{DEL}", "Dummy"
Application.OnKey "+{INSERT}", "Dummy"
Application.CellDragAndDrop = False
Application.OnDoubleClick = "Dummy"
CommandBars("ToolBar List").Enabled = False
 
Upvote 0
If PasteSpecial is left enabled, users will not be restricted to only pasting values.

All the PasteSpecial options will be available, including Copy>PasteSpecial>All which is the same as Copy>Paste.
 
Upvote 0
Ponsonby said:
If PasteSpecial is left enabled, users will not be restricted to only pasting values.

All the PasteSpecial options will be available, including Copy>PasteSpecial>All which is the same as Copy>Paste.
Click to expand...

Spot on, Ponsonby.

You are obviously thinking more clearly than me today.
 
Upvote 0
Disable Cut and Paste

Andrew Poulsom said:
You will need to disable the Controls - CutCopyMode = False just clears the clipboard. Pick what you need from here:

http://www.xcelfiles.com/VBA_Quick13.html
Click to expand...

This seems to be exactly what I am looking for. I want to be able to completely disable Cut, Paste, and Copy. My question is, " how do I properly implement this code?" Where does it need to be inserted to work correctly? I was using the CutCopyMode = False which seemed to work fine when I tested it, but when I released it for general use all users were able to cut & paste whenever and wherever they wanted.
[/b]
 
Upvote 0
Disable Cut and Paste

Hey guys!!!
I figured out where things needed to go and it works GREAT!!! :D I have a small question regarding a slight modification. Is it possible to allow/reactivate the autofill feature while not allowing CopyCutPaste?? I can live quite happily with it the way it is. It would just make things a little easier as I often need to enter sequential numbers or items.

TIA
 
Upvote 0
Ponsonby said:
Here's another possibility (not thoroughly tested) :-


Function GetFromClipboard() As Variant
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard
On Error Resume Next
GetFromClipboard = MyDataObj.GetText()
On Error GoTo 0
End Function
Click to expand...

I have a similar situation as the person that posted this and I like this code because mine is only needed for a specific range within on sheet in a workbook. However I kep getting this error:

Complie error:
User-defined type not defined

and it highlights this part of your function
MyDataObj As New DataObject

Can you help me figure out how to correct this? Otherwise, the whole thing works brilliantly :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

dreid1011
  • Question
Excel Crashing after Worksheet_Change code
Replies
1
Views
171
dreid1011
dreid1011
Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
244
bobsan42
bobsan42
L
  • Question
VBA to autosave and close after a set amount of time
Replies
3
Views
165
Anthony47
A
G
  • Question
Macro Code Help for multiple worksheets.
Replies
1
Views
193
Georgiboy
Georgiboy
W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
181
Worksong
W

Forum statistics

Threads
1,215,389
Messages
6,124,665
Members
449,178
Latest member
Emilou

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