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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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

G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
889
jkpieterse
jkpieterse
D
  • Question Question
VBA to lock Cell after Data Entry but still Allow Sort & Filter ?
Replies
3
Views
274
Jeffrey Mahoney
Jeffrey Mahoney
K
  • Solved
Undo Stack gets cleared running VBA - Possible Solution to fix this?
Replies
5
Views
163
kellman
K
R
  • Solved
Display the result in the active cell in another cell
Replies
6
Views
240
rvan07
R
E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
205
Alex Blakenburg
A

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

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