VBA for PasteSpecial Values ONLY???

[Mister H]

Hi All:

I was wondering if there is some VBA code that I can put in my spreadsheet that would only allow Paste Special Values?

I want to make sure that the user can not simply cut and paste. If possible it would be nice if the user doesn't know what is going on all they need to know is that they can copy and paste.

Is there a seamless way to accomplish this?

If you need further info just let me know.
THANKS,
Mark

 

[AgentSmith]

I don't think there's a quick way to do this. Perhaps you could work around it though, e.g.

Lock the sheet entirely
Write a macro that unlocks the sheet, pastes values and then locks it again
Reassign CTRL+V so it runs this macro

Reassigning CTRL+V should be done with caution! If users have other spreadsheets open simultaneously then you risk upsetting them!

 

[AgentSmith]

Hmm - not sure if you can reassign it after all. Would CTRL+SHIFT+V do?

Edit: you can with Application.OnKey.

 

[Mister H]

THANKS for your reply Mike. I will experiment with your solution and see if this will work for the user.

Have a GREAT day,
Mark

 

[AgentSmith]

No problem. In order to reduce risk of upsetting your colleagues, use the Workbook_Activate event to do the reassigning of CTRL+V. Then reset it in the Workbook_Deactivate event (and Workbook_BeforeClose for that matter ...).

 

[Mister H]

THANKS again Mike...

Someone else pointed me to wards this code (I had a similar Post to the one you answered).

This code changes the Copy Paste to Copy PasteSpecial Values. I played with it a bit and it seems to work pretty good and it is seamless. I guess the only problem arises if someone actually wants to cut and paste all formats etc. Anyway, I thought I would share this with you and anyone that might happen to come across this post.

Does anyone know if this code can be altered to apply ONLY to a sheet titled (Meeting Minutes)?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
    Dim UndoString As String
    Dim srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        
        Exit Sub
        
    End If
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
            
            
    If UndoString = "Auto Fill" Then
        
        Set srce = Selection
        
        srce.Copy
        
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                    
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
        
    Else
    
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                    
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

THANKS,
Mark

 

[AgentSmith]

Nice :D

The extension you're after could be along these lines:

If Target.Parent.Name <> "Meeting Minutes" Then
  Application.CommandBars("Standard").Controls("&Paste").Execute
ElseIf ...

right after "Application.Undo".

 

[Mister H]

Hi AGAIN Mike.

THANKS for that piece of code however I could not get it to work when I placed it where you had suggested. I then put it at the beginning and it seems to work however I am not sure if I have compromised anything else in the formula because I am lacking in VBA. The code below seems to work but it pops up another menu when the sheet is not titled Meeting Minutes. The menu is the Paste Menu and it is just asking how I want to Paste. Can you tell me how to get rid of that piece and just have the code do a standard Paste? Hope this makes sense... Here is the modified code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
 
    Dim UndoString As String
    Dim srce As Range
    On Error GoTo err_handler
 
'I ADDED YOUR CODE HERE (Red)
  If Target.Parent.Name <> "Meeting Minutes" Then
  Application.CommandBars("Standard").Controls("&Paste").Execute
 
  Else
 
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
 
        Exit Sub
 
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
 
'It didn't seem to work for me when I placed it here???
'    If Target.Parent.Name <> "Meeting Minutes" Then
'    Application.CommandBars("Standard").Controls("&Paste").Execute
 
'    Else
 
    If UndoString = "Auto Fill" Then
 
        Set srce = Selection
 
        srce.Copy
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
 
    Else
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
    End If
    End If
     'End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

THANKS Again Mike,
Take Care,
Mark

 

[AgentSmith]

On reflection I don't think you need the Paste line I gave you.

Try this:

If Target.Parent.Name <> "Meeting Minutes" Then
    Exit Sub
End If

UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
    Exit Sub
End If

 

[Mala]

Exactly my thought when I responded to:
http://www.mrexcel.com/forum/showthread.php?t=355800