Disable paste but allow pastespecial values

[Iridium]

Found this code somewhere on the web a while ago

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

 

[Jaafar Tribak]

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

You get that Compile error because you need to have a reference to the MSForms library which contains the DataObject Class.

To solve the problem ,either add a UserForm to your Project or amend your code as follows :

Function GetFromClipboard() As Variant
    Set MyDataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
    MyDataObj.GetFromClipboard
    On Error Resume Next
    GetFromClipboard = MyDataObj.GetText()
    On Error GoTo 0
End Function

 

[whitemexican1]

Thanks Jaafar,

That works now, unfortunately now that the Function can be called, I am getting problems with the original code that called it, below the function call.

This code below causes a message box coming up whenever a cell in the range is selected, not just when someone tries to paste something. That's an issue. I suppose I could just delete that part... right?
Then below that it I get an error that says there is an ELSE without an IF.

And lastly, for some reason when I pasted this code in to my sheet, it has caused me to lose my Paste Special capabilities in any part of excel. How can I fix that. I need that. Help?


Originally posted by PONSONBY:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim VRange As Range, cell As Range
Set VRange = Range("perc_complete_entry")
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