How to Undo with a Paste special as values VBA

[Dazzybeeguy]

I have the following code that paste the copied data as values when the user clicks in a cell.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub

The problem is if they open the spreadsheet and click in a cell whatever is in their clipboard pastes in, there is no option to Undo / use Ctrl Z as its not there.

How can I give them the option to undo ???

Thanks

 

[Herakles]

You could clear the clipboard when the workbook is opened.

Put this code in a standard code module.

#If VBA7 Then
    Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
    Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    Public Declare Function CloseClipboard Lib "user32" () As Long
#End If

Public Sub subClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

And this in the the Workbook Open event.

Private Sub Workbook_Open()

    Call subClearClipboard
        
End Sub

If I was you I would check to see what is in the clipboard before it is copied / pasted to the cell and
only if the cell is in an acceptable range of cells.

Use this function to put text into the clipboard or retrieve it.

This line :
strText = fncClipboard
will place the contents of the clipboard into the strText variable and this line
fncClipboard("Text")
will place the word "Text" into the clipboard.

Public Function fncClipboard(Optional StoreText As String) As String
Dim x As Variant

' Store as variant for 64-bit VBA support
  x = StoreText

' Create HTMLFile Object
  With CreateObject("htmlfile")
    With .parentWindow.clipboardData
      Select Case True
        Case Len(StoreText)
          ' Write to the clipboard
            .setData "text", x
        Case Else
          ' Read from the clipboard (no variable passed through)
           If IsNull(.GetData("text")) Then
                fncClipboard = ""
           Else
                fncClipboard = .GetData("text")
           End If
      End Select
    End With
  End With

End Function