Allow only unformatted values after pasting from any clipboard using any shortkey or button

  • 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/her 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


    Private Sub  Workbook_BeforeClose(Cancel As Boolean)
    Application.CellDragAndDrop  = True
    End Sub


    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 by using in Module1 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:

    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 how 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 Pastebutton 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!:thumbup:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!