Allow only unformatted values when pasting

[renevromzicafe]

Hi,

This is my first time on this forum, so maybe I’m posting an issue of which the solution is too simple for words. I sincerely hope that this is the case ?.

I made an Office 365 Excelfile in which a person has to register some data. Because I put some formulas in the file (worksheet) he/she can check whether his input is valid. These formulas are secured so he cannot alter them or delete them. Also I use conditional formatting to make these data presentable.

When this person just types the data in the worksheet, there is no problem whatsoever.

Unfortunately, he sometimes likes to move certain cells or cuts cells to paste them elsewhere. The result is that the formulas do not work properly anymore. He also copies data from other Office-applications or from an internet page which means that conditional formatting is altered.

To make the registration of data in this file idiot proof I took the following steps in VBA.

To prohibit the moving of cells I used in ThisWorkbook:

Private Sub Workbook_Open()

Application.CellDragAndDrop = False

End Sub

And:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CellDragAndDrop = True

End Sub

Simple.

To prohibit the cutting of cells I used in the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.CutCopyMode = xlCut Then

Application.CutCopyMode = False

End If

End Sub

Also very simple.

To prohibit the copying of data with specific formatting I redefined the CTRL+v shortkey in Module1 by using the following code in which the first PasteSpecial is to paste unformatted values when pasting from the Office-clipboard and the second PasteSpecial to paste unformatted values when pasting from the Windows-clipboard:

Sub Pastevalues ()

On Error Resume Next

Selection.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

If Err.Number = 0 Then

Exit Sub

Else

On Error GoTo Fault

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End If

Exit Sub

Fault:

MsgBox "This didn’t work!", _

vbCritical, Title:="Sorry..."

Resume Next

End Sub

So far so good.

However, when this person uses the Paste-button on the Home toolbar after pasting from any clipboard still the values come with their specific formatting. I just cannot figure out to prevent the use of this button and make the pasting of values with specific formatting absolutely impossible.

I thought of the Application.Undo expression, but this didn’t work.

I thought of redefining the Pastbutton with something like:

Application.CommandBars("Home").Controls("Paste").OnAction = "Pastevalues"

but this didn’t work either (I think this only worked in previous versions of Excel).

Does anyone on this forum has the solution to my problem?

Many thanks!

 

[Yongle]

Try putting this in sheet code window

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Mark As String
    Mark = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(Mark, 5) = "Paste" Then Application.Undo
End Sub

 

[renevromzicafe]

Try putting this in sheet code window

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Mark As String
    Mark = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(Mark, 5) = "Paste" Then Application.Undo
End Sub

Sorry, but I get the following error:

Run-Time Error 5: Invalid Procedure Call or Argument​

on 'Mark = Application.CommandBars("Standard").Controls("&Undo").List(1)...

 

[Yongle]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Mark As String
    On Error Resume Next
    Mark = Application.CommandBars("Standard").Controls("&Undo").List(1)
    On Error GoTo 0
    If Left(Mark, 5) = "Paste" Then Application.Undo
End Sub

 

[renevromzicafe]

Thanks. This indeed resolves the error, but not my initial problem.