Allow only unformatted values when pasting

renevromzicafe

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
Private Sub Workbook_Open()

Application.CellDragAndDrop = False

End Sub

And:

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CellDragAndDrop = True

End Sub

Simple.

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

VBA Code:
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:

VBA Code:
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:
VBA Code:
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!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try putting this in sheet code window
VBA Code:
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

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try putting this in sheet code window
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Rich (BB code):
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

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thanks. This indeed resolves the error, but not my initial problem.;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top