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:
To prohibit the cutting of cells I used in the worksheet:
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:
- Sub Pastevalues ()
- On Error Resume Next
- Selection.PasteSpecial Paste:=xlPasteValues
- Application.CutCopyMode = False
- If Err.Number = 0 Then
- Exit Sub
- On Error GoTo Fault
- ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
- End If
- Exit Sub
- 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 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?